/*
Navicat SQL Server Data Transfer

Source Server         : CMRS Local
Source Server Version : 105000
Source Host           : localhost:1433
Source Database       : cmrsfinal
Source Schema         : dbo

Target Server Type    : SQL Server
Target Server Version : 105000
File Encoding         : 65001

Date: 2012-05-02 00:50:14
*/


-- ----------------------------
-- Table structure for [accountcode]
-- ----------------------------
CREATE TABLE [accountcode] (
[id] int NOT NULL IDENTITY(1,1) ,
[accountcodedesc] varchar(255) NULL ,
[accountcode] int NULL ,
[usageinstructions] varchar(255) NULL ,
[createdate] datetime NULL ,
[createdby] int NULL ,
[lastupdated] datetime NULL ,
[lastupdatedby] int NULL 
)


GO
DBCC CHECKIDENT(N'[accountcode]', RESEED, 894)
GO

-- ----------------------------
-- Table structure for [actionablealerts]
-- ----------------------------
CREATE TABLE [actionablealerts] (
[id] int NOT NULL ,
[module] varchar(255) NULL ,
[type] varchar(255) NULL ,
[createdby] int NULL ,
[createdate] datetime NULL ,
[lastupdated] datetime NULL ,
[lastupdatedby] int NULL ,
[description] varchar(500) NULL 
)


GO

-- ----------------------------
-- Table structure for [actionablealertsmessage]
-- ----------------------------
CREATE TABLE [actionablealertsmessage] (
[id] int NOT NULL IDENTITY(1,1) ,
[date] datetime NULL ,
[message] varchar(255) NULL ,
[url] varchar(255) NULL ,
[actionablealertsid] int NULL ,
[programofficer] int NULL ,
[regionaldirector] int NULL ,
[programassistant] int NULL ,
[regionid] int NULL 
)


GO

-- ----------------------------
-- Table structure for [actionaltertchangenotifications]
-- ----------------------------
CREATE TABLE [actionaltertchangenotifications] (
[id] int NOT NULL IDENTITY(1,1) ,
[module] varchar(50) NULL ,
[noticetype] varchar(15) NULL ,
[noticedate] datetime NULL ,
[message] varchar(1000) NULL ,
[noticequery] varchar(4000) NULL ,
[createdate] datetime NULL ,
[createdby] int NULL ,
[lastupdatedby] int NULL ,
[lastupdated] datetime NULL ,
[completed] varchar(1) NULL ,
[alerttypeid] int NULL ,
[changetypeid] int NULL 
)


GO

-- ----------------------------
-- Table structure for [actionaltertchangenotifications_useractionalertschangess]
-- ----------------------------
CREATE TABLE [actionaltertchangenotifications_useractionalertschangess] (
[actionaltertchangenotifications] int NOT NULL ,
[useractionalertschangess] int NOT NULL 
)


GO

-- ----------------------------
-- Table structure for [alerttype]
-- ----------------------------
CREATE TABLE [alerttype] (
[id] int NOT NULL IDENTITY(1,1) ,
[typename] varchar(100) NULL ,
[createdate] datetime NULL ,
[createdby] int NULL ,
[lastupdated] datetime NULL ,
[lastupdatedby] int NULL 
)


GO
DBCC CHECKIDENT(N'[alerttype]', RESEED, 12)
GO

-- ----------------------------
-- Table structure for [audits]
-- ----------------------------
CREATE TABLE [audits] (
[id] int NOT NULL IDENTITY(1,1) ,
[createdate] datetime NULL ,
[createdby] int NULL ,
[lastupdated] datetime NULL ,
[lastupdatedby] int NULL ,
[impaction] varchar(50) NULL ,
[impduedate] datetime NULL ,
[impperformed] datetime NULL ,
[impreceived] datetime NULL ,
[impclosedtofile] varchar(255) NULL ,
[year] int NULL ,
[acronym] varchar(50) NULL ,
[newgrantee] varchar(1) NULL ,
[cipeorgranteesolicitsaudit] varchar(50) NULL ,
[riskanalysisrecdpo] datetime NULL ,
[riskassessment] float(53) NULL ,
[audittype] varchar(50) NULL ,
[smponly] varchar(1) NULL ,
[imp] varchar(1) NULL ,
[interimaudit] varchar(1) NULL ,
[iareceived] datetime NULL ,
[iaapproved] datetime NULL ,
[iareturnedforcorrections] datetime NULL ,
[iaclosedtofile] datetime NULL ,
[iaresolutionoffindingsdue] datetime NULL ,
[iaresolutionoffindingsreceived] datetime NULL ,
[iaresolutionoffindingsapproved] datetime NULL ,
[iarevisedresolutionoffindingsdue] datetime NULL ,
[iarevisedresolutionreceived] datetime NULL ,
[iarevisedresolutionapproved] datetime NULL ,
[finalaudit] varchar(1) NULL ,
[faselectionmemoduedate] datetime NULL ,
[faselectionmemoreceived] datetime NULL ,
[faapproved] datetime NULL ,
[fareturnedforcorrections] varchar(50) NULL ,
[faresolutionoffindingsdue] datetime NULL ,
[faresolutionoffindingsreceived] datetime NULL ,
[faresolutionoffindingsapproved] datetime NULL ,
[farevisedresolutionoffindingsdue] datetime NULL ,
[farevisedresolutionoffindingsreceived] datetime NULL ,
[farevisedresolutionapproved] datetime NULL ,
[faclosedtofile] datetime NULL ,
[impnotes] varchar(5000) NULL ,
[projectid] int NULL ,
[internalmonitoring] tinyint NULL ,
[fafirm] varchar(255) NULL ,
[famemonotes] varchar(500) NULL ,
[farevisedresolutiondue] datetime NULL ,
[farevisedresolutionoffindingsapproved] datetime NULL ,
[farevisedresolutionreceived] datetime NULL ,
[faselectionmemoapproved] datetime NULL ,
[faselectionmemodue] datetime NULL ,
[fasolicitor] varchar(255) NULL ,
[iafirm] varchar(255) NULL ,
[iamemonotes] varchar(500) NULL ,
[iarevisedresolutiondue] datetime NULL ,
[iarevisedresolutionoffindingsapproved] datetime NULL ,
[iarevisedresolutionoffindingsreceived] datetime NULL ,
[iaselectionmemoapproved] datetime NULL ,
[iaselectionmemodue] datetime NULL ,
[iaselectionmemoreceived] datetime NULL ,
[iasolicitor] varchar(255) NULL ,
[imprequired] tinyint NULL ,
[iadue] datetime NULL ,
[fadue] datetime NULL ,
[fareceived] datetime NULL 
)


GO
DBCC CHECKIDENT(N'[audits]', RESEED, 3061)
GO

-- ----------------------------
-- Table structure for [Cacheactionablealertsmessage]
-- ----------------------------
CREATE TABLE [Cacheactionablealertsmessage] (
[id] int NOT NULL IDENTITY(1,1) ,
[date] datetime NULL ,
[message] varchar(255) NULL ,
[url] varchar(255) NULL ,
[actionablealertsid] int NULL ,
[programofficer] int NULL ,
[regionaldirector] int NULL ,
[programassistant] int NULL ,
[regionid] int NULL 
)


GO
DBCC CHECKIDENT(N'[Cacheactionablealertsmessage]', RESEED, 250)
GO

-- ----------------------------
-- Table structure for [categorylinks]
-- ----------------------------
CREATE TABLE [categorylinks] (
[id] int NOT NULL IDENTITY(1,1) ,
[linkid] int NULL ,
[linkcategoryid] int NULL 
)


GO
DBCC CHECKIDENT(N'[categorylinks]', RESEED, 46)
GO

-- ----------------------------
-- Table structure for [changenotificationsmessage]
-- ----------------------------
CREATE TABLE [changenotificationsmessage] (
[id] int NOT NULL IDENTITY(1,1) ,
[date] datetime NULL ,
[message] varchar(255) NULL ,
[url] varchar(255) NULL ,
[changenotificationsid] int NULL ,
[programofficer] int NULL ,
[regionaldirector] int NULL ,
[programassistant] int NULL ,
[regionid] int NULL 
)


GO
DBCC CHECKIDENT(N'[changenotificationsmessage]', RESEED, 111)
GO

-- ----------------------------
-- Table structure for [changenotificationtype]
-- ----------------------------
CREATE TABLE [changenotificationtype] (
[id] int NOT NULL IDENTITY(1,1) ,
[typename] varchar(100) NULL ,
[createdate] datetime NULL ,
[createdby] int NULL ,
[lastupdated] datetime NULL ,
[lastupdatedby] int NULL ,
[module] varchar(255) NULL ,
[type] varchar(255) NULL ,
[description] varchar(500) NULL 
)


GO
DBCC CHECKIDENT(N'[changenotificationtype]', RESEED, 10)
GO

-- ----------------------------
-- Table structure for [country]
-- ----------------------------
CREATE TABLE [country] (
[id] int NOT NULL IDENTITY(1,1) ,
[countryname] varchar(100) NULL ,
[createdate] datetime NULL ,
[createdby] int NULL ,
[lastupdated] datetime NULL ,
[lastupdatedby] int NULL ,
[regionid] int NULL ,
[programassistant] int NULL ,
[programofficer] int NULL 
)


GO
DBCC CHECKIDENT(N'[country]', RESEED, 188)
GO

-- ----------------------------
-- Table structure for [dashboardlink]
-- ----------------------------
CREATE TABLE [dashboardlink] (
[id] int NOT NULL ,
[linkName] varchar(255) NULL ,
[url] varchar(255) NULL ,
[count] int NULL ,
[createdby] int NULL ,
[createdate] datetime NULL ,
[lastupdated] datetime NULL ,
[lastupdatedby] int NULL 
)


GO

-- ----------------------------
-- Table structure for [donor]
-- ----------------------------
CREATE TABLE [donor] (
[id] int NOT NULL IDENTITY(1,1) ,
[donorname] varchar(100) NULL ,
[donoracronym] varchar(100) NULL ,
[url] varchar(400) NULL ,
[address1] varchar(100) NULL ,
[address2] varchar(100) NULL ,
[city] varchar(50) NULL ,
[state] int NULL ,
[zipcode] varchar(20) NULL ,
[contactname] varchar(100) NULL ,
[contactphone] varchar(20) NULL ,
[contactfax] varchar(20) NULL ,
[contactemail] varchar(100) NULL ,
[createdby] int NULL ,
[createdate] datetime NULL ,
[lastupdated] datetime NULL ,
[lastupdatedby] int NULL ,
[countryid] int NULL 
)


GO
DBCC CHECKIDENT(N'[donor]', RESEED, 133)
GO

-- ----------------------------
-- Table structure for [donoraward]
-- ----------------------------
CREATE TABLE [donoraward] (
[id] int NOT NULL IDENTITY(1,1) ,
[donorawardnumber] varchar(50) NULL ,
[donorawarddesc] varchar(500) NULL ,
[startdate] datetime NULL ,
[enddate] datetime NULL ,
[attachment] varchar(512) NULL ,
[fiscalyear] int NULL ,
[attachmentname] varchar(100) NULL ,
[attachmentcontenttype] varchar(100) NULL ,
[createdby] int NULL ,
[createdate] datetime NULL ,
[lastupdated] datetime NULL ,
[lastupdatedby] int NULL ,
[obligedamount] numeric(18,3) NULL ,
[memonotes] varchar(5000) NULL ,
[directfunds] int NULL ,
[indirectfunds] int NULL ,
[donorid] int NULL ,
[donorawardtypeid] int NULL ,
[donorawardstatusid] int NULL 
)


GO
DBCC CHECKIDENT(N'[donoraward]', RESEED, 1395)
GO

-- ----------------------------
-- Table structure for [donorawardmodification]
-- ----------------------------
CREATE TABLE [donorawardmodification] (
[id] int NOT NULL IDENTITY(1,1) ,
[attachment] varchar(512) NULL ,
[createdate] datetime NULL ,
[createdby] int NULL ,
[lastupdated] datetime NULL ,
[lastupdatedby] int NULL ,
[donorawardid] int NULL ,
[modificationtypeid] int NOT NULL ,
[modifiedfrom] varchar(255) NOT NULL ,
[modifiedto] varchar(255) NOT NULL ,
[modifieddate] datetime NOT NULL ,
[modifiedby] int NOT NULL 
)


GO
DBCC CHECKIDENT(N'[donorawardmodification]', RESEED, 42)
GO

-- ----------------------------
-- Table structure for [donorawardstatus]
-- ----------------------------
CREATE TABLE [donorawardstatus] (
[id] int NOT NULL IDENTITY(1,1) ,
[statusname] varchar(100) NULL ,
[createdby] int NULL ,
[createdate] datetime NULL ,
[lastupdatedby] int NULL ,
[lastupdated] datetime NULL 
)


GO
DBCC CHECKIDENT(N'[donorawardstatus]', RESEED, 6)
GO

-- ----------------------------
-- Table structure for [donorawardstatustransitionsallowed]
-- ----------------------------
CREATE TABLE [donorawardstatustransitionsallowed] (
[id] int NOT NULL IDENTITY(1,1) ,
[createdby] int NULL ,
[createdate] datetime NULL ,
[lastupdatedby] int NULL ,
[lastupdated] datetime NULL ,
[goingtostatus] int NULL ,
[startingfromstatus] int NULL 
)


GO

-- ----------------------------
-- Table structure for [donorawardtype]
-- ----------------------------
CREATE TABLE [donorawardtype] (
[id] int NOT NULL IDENTITY(1,1) ,
[donorawardtypedesc] varchar(500) NULL ,
[donorawardtype] varchar(100) NULL ,
[createdby] int NULL ,
[createdate] datetime NULL ,
[lastupdated] datetime NULL ,
[lastupdatedby] int NULL ,
[deleted] varchar(1) NULL 
)


GO
DBCC CHECKIDENT(N'[donorawardtype]', RESEED, 8)
GO

-- ----------------------------
-- Table structure for [fieldoffice]
-- ----------------------------
CREATE TABLE [fieldoffice] (
[id] int NOT NULL IDENTITY(1,1) ,
[fieldofficecode] varchar(25) NULL ,
[fieldofficename] varchar(100) NULL ,
[address1] varchar(100) NULL ,
[address2] varchar(100) NULL ,
[city] varchar(50) NULL ,
[contactemail] varchar(100) NULL ,
[contactname] varchar(100) NULL ,
[contactphone] varchar(20) NULL ,
[contactfax] varchar(20) NULL ,
[emailaddress] varchar(100) NULL ,
[phone] varchar(20) NULL ,
[fax] varchar(20) NULL ,
[stateprovince] int NULL ,
[inactive] varchar(1) NULL ,
[createdate] datetime NULL ,
[createdby] int NULL ,
[lastupdated] datetime NULL ,
[lastupdatedby] int NULL ,
[countryid] int NULL ,
[regionid] int NULL 
)


GO
DBCC CHECKIDENT(N'[fieldoffice]', RESEED, 8)
GO

-- ----------------------------
-- Table structure for [imp]
-- ----------------------------
CREATE TABLE [imp] (
[id] int NOT NULL IDENTITY(1,1) ,
[createdate] datetime NULL ,
[createdby] int NULL ,
[lastupdated] datetime NULL ,
[lastupdatedby] int NULL ,
[impaction] varchar(50) NULL ,
[impduedate] datetime NULL ,
[impperformed] datetime NULL ,
[impreceived] datetime NULL ,
[impclosedtofile] datetime NULL ,
[projectreportsid] int NULL ,
[projectid] int NULL ,
[auditsid] int NULL 
)


GO
DBCC CHECKIDENT(N'[imp]', RESEED, 329)
GO

-- ----------------------------
-- Table structure for [keyword]
-- ----------------------------
CREATE TABLE [keyword] (
[id] int NOT NULL IDENTITY(1,1) ,
[keywordcode] varchar(30) NULL ,
[keyworddesc] varchar(500) NULL ,
[createdby] int NULL ,
[createdate] datetime NULL ,
[lastupdatedby] int NULL ,
[lastupdated] datetime NULL 
)


GO
DBCC CHECKIDENT(N'[keyword]', RESEED, 129)
GO

-- ----------------------------
-- Table structure for [link]
-- ----------------------------
CREATE TABLE [link] (
[id] int NOT NULL IDENTITY(1,1) ,
[displayorder] int NULL ,
[displayname] varchar(500) NULL ,
[url] varchar(400) NULL ,
[createdate] datetime NULL ,
[createdby] int NULL ,
[lastupdated] datetime NULL ,
[lastupdateby] int NULL 
)


GO
DBCC CHECKIDENT(N'[link]', RESEED, 94)
GO

-- ----------------------------
-- Table structure for [linkcategory]
-- ----------------------------
CREATE TABLE [linkcategory] (
[id] int NOT NULL IDENTITY(1,1) ,
[displayorder] int NULL ,
[displayname] varchar(500) NULL ,
[createdate] datetime NULL ,
[createdby] int NULL ,
[lastupdated] datetime NULL ,
[lastupdateby] int NULL 
)


GO
DBCC CHECKIDENT(N'[linkcategory]', RESEED, 12)
GO

-- ----------------------------
-- Table structure for [modificationtype]
-- ----------------------------
CREATE TABLE [modificationtype] (
[id] int NOT NULL IDENTITY(1,1) ,
[modificationtypecode] varchar(25) NULL ,
[modificationdesc] varchar(500) NULL ,
[createdate] datetime NULL ,
[createdby] int NULL ,
[lastupdated] datetime NULL ,
[lastupdatedby] int NULL 
)


GO
DBCC CHECKIDENT(N'[modificationtype]', RESEED, 13)
GO

-- ----------------------------
-- Table structure for [organizationtype]
-- ----------------------------
CREATE TABLE [organizationtype] (
[id] int NOT NULL IDENTITY(1,1) ,
[organizationtypename] varchar(100) NULL ,
[organizationtypedesc] varchar(500) NULL ,
[createdby] int NULL ,
[createdate] datetime NULL ,
[lastupdatedby] int NULL ,
[lastupdated] datetime NULL 
)


GO
DBCC CHECKIDENT(N'[organizationtype]', RESEED, 7)
GO

-- ----------------------------
-- Table structure for [partner]
-- ----------------------------
CREATE TABLE [partner] (
[id] int NOT NULL IDENTITY(1,1) ,
[createdate] datetime NULL ,
[createdby] int NULL ,
[lastupdated] datetime NULL ,
[lastupdatedby] int NULL ,
[partnername] varchar(100) NULL ,
[address1] varchar(100) NULL ,
[address2] varchar(100) NULL ,
[city] varchar(50) NULL ,
[state] varchar(25) NULL ,
[zipcode] varchar(20) NULL ,
[phone] varchar(20) NULL ,
[fax] varchar(20) NULL ,
[email] varchar(50) NULL ,
[url] varchar(400) NULL ,
[contactfirstname] varchar(100) NULL ,
[contactmiddlename] varchar(100) NULL ,
[contactlastname] varchar(100) NULL ,
[contactphone] varchar(20) NULL ,
[contactfax] varchar(20) NULL ,
[contactemail] varchar(50) NULL ,
[countryid] int NULL ,
[stateid] int NULL 
)


GO
DBCC CHECKIDENT(N'[partner]', RESEED, 1080)
GO

-- ----------------------------
-- Table structure for [payee]
-- ----------------------------
CREATE TABLE [payee] (
[id] int NOT NULL IDENTITY(1,1) ,
[payeename] varchar(50) NULL ,
[payeeaddress1] varchar(100) NULL ,
[payeetype] varchar(25) NULL ,
[payeeaddress2] varchar(100) NULL ,
[payeecity] varchar(50) NULL ,
[payeestate] varchar(25) NULL ,
[payeezipcode] varchar(20) NULL ,
[payeeemail] varchar(100) NULL ,
[payeephone] varchar(20) NULL ,
[payeefax] varchar(20) NULL ,
[payeeurl] varchar(400) NULL ,
[beneficiarytname] varchar(100) NULL ,
[beneficiaryaddressline1] varchar(100) NULL ,
[beneficiaryaddressline2] varchar(100) NULL ,
[beneficiarycity] varchar(50) NULL ,
[beneficiarystate] varchar(25) NULL ,
[beneficiaryzipcode] varchar(20) NULL ,
[beneficiarycountrycode] varchar(3) NULL ,
[phonenumber] varchar(20) NULL ,
[bankname] varchar(100) NULL ,
[bankcode] varchar(25) NULL ,
[chipscode] varchar(25) NULL ,
[accountnumber] varchar(25) NULL ,
[bankaddressline1] varchar(100) NULL ,
[bankaddressline2] varchar(100) NULL ,
[bankcity] varchar(50) NULL ,
[bankstate] varchar(25) NULL ,
[bankzipcode] varchar(20) NULL ,
[bankcountrycode] varchar(25) NULL ,
[intermediarybank] varchar(100) NULL ,
[intermediarybankcode] varchar(25) NULL ,
[specialinstructions] varchar(200) NULL ,
[createdate] datetime NULL ,
[createdby] int NULL ,
[lastupdated] datetime NULL ,
[lastupdatedby] int NULL ,
[countryid] int NULL ,
[beneficiaryphonenumber] varchar(20) NULL ,
[payeestateid] int NULL ,
[inactive] char(1) NULL ,
[employertaxid] varchar(255) NULL 
)


GO
DBCC CHECKIDENT(N'[payee]', RESEED, 4998)
GO

-- ----------------------------
-- Table structure for [paymentrequest]
-- ----------------------------
CREATE TABLE [paymentrequest] (
[id] int NOT NULL IDENTITY(1,1) ,
[paymentidentifiedinformation] varchar(500) NULL ,
[receivedfromaccountingdate] datetime NULL ,
[paymentconfirmationnumber] varchar(50) NULL ,
[purposeofexpenditure] varchar(500) NULL ,
[paymentmethod] varchar(50) NULL ,
[specialinstructions] varchar(200) NULL ,
[statuscode] varchar(50) NULL ,
[createdate] datetime NULL ,
[createdby] int NULL ,
[lastupdated] datetime NULL ,
[lastupdatedby] int NULL ,
[memonotes] varchar(5000) NULL ,
[deliverablesreceived] varchar(1) NULL ,
[invoicenumber] varchar(50) NULL ,
[invoicedate] datetime NULL ,
[amount] numeric(18,3) NULL ,
[paidamount] numeric(18,3) NULL ,
[comments] varchar(1000) NULL ,
[dateinfinance] datetime NULL ,
[daterejected] datetime NULL ,
[dateresubmitted] datetime NULL ,
[dateforwardedforsignature] datetime NULL ,
[pendingpaymentdate] datetime NULL ,
[datecancelled] datetime NULL ,
[datepaid] datetime NULL ,
[currentlyresponsible] varchar(100) NULL ,
[rejectreason] varchar(500) NULL ,
[paymenttypeid] int NULL ,
[payeeid] int NULL ,
[countryid] int NULL ,
[datesubmittedforpayment] datetime NULL ,
[reasonrejected] varchar(500) NULL ,
[datecancelrequested] datetime NULL ,
[_6570verify] char(1) NULL ,
[sourcedocumenttype] varchar(255) NULL 
)


GO
DBCC CHECKIDENT(N'[paymentrequest]', RESEED, 22342)
GO

-- ----------------------------
-- Table structure for [paymentrequestlineitem]
-- ----------------------------
CREATE TABLE [paymentrequestlineitem] (
[id] int NOT NULL IDENTITY(1,1) ,
[lineitemnumber] int NULL ,
[paymentrequestamount] numeric(15,2) NULL ,
[createdate] datetime NULL ,
[createdby] int NULL ,
[lastupdated] datetime NULL ,
[lastupdatedby] int NULL ,
[paymentrequestid] int NULL ,
[accountcodeid] int NULL ,
[projectid] int NULL 
)


GO
DBCC CHECKIDENT(N'[paymentrequestlineitem]', RESEED, 46929)
GO

-- ----------------------------
-- Table structure for [paymentrequestmodification]
-- ----------------------------
CREATE TABLE [paymentrequestmodification] (
[id] int NOT NULL IDENTITY(1,1) ,
[previousvalue] varchar(15) NULL ,
[newvalue] varchar(15) NULL ,
[attachment] varchar(512) NULL ,
[createdate] datetime NULL ,
[createdby] int NULL ,
[lastupdated] datetime NULL ,
[lastupdatedby] int NULL ,
[paymentrequestid] int NULL ,
[modificationtypeid] int NULL 
)


GO

-- ----------------------------
-- Table structure for [paymentrequeststatus]
-- ----------------------------
CREATE TABLE [paymentrequeststatus] (
[id] int NOT NULL IDENTITY(1,1) ,
[effectivedate] datetime NULL ,
[createdate] datetime NULL ,
[createdby] int NULL ,
[lastupdated] datetime NULL ,
[lastupdatedby] int NULL ,
[previousvalue] varchar(15) NULL ,
[newvalue] varchar(15) NULL ,
[paymentstatustypeid] int NULL ,
[paymentrequestid] int NULL 
)


GO

-- ----------------------------
-- Table structure for [paymentrequeststatustransitions]
-- ----------------------------
CREATE TABLE [paymentrequeststatustransitions] (
[id] int NOT NULL IDENTITY(1,1) ,
[createdby] int NULL ,
[createdate] datetime NULL ,
[lastupdatedby] int NULL ,
[lastupdated] datetime NULL ,
[statustransitiondesc] varchar(500) NULL ,
[goingtostatus] int NULL ,
[startingfromstatus] int NULL 
)


GO

-- ----------------------------
-- Table structure for [paymentstatustype]
-- ----------------------------
CREATE TABLE [paymentstatustype] (
[id] int NOT NULL IDENTITY(1,1) ,
[paymentstatusname] varchar(100) NULL ,
[paymentstatusdesc] varchar(500) NULL ,
[createdate] datetime NULL ,
[createdby] int NULL ,
[lastupdated] datetime NULL ,
[lastupdatedby] int NULL 
)


GO
DBCC CHECKIDENT(N'[paymentstatustype]', RESEED, 9)
GO

-- ----------------------------
-- Table structure for [paymenttype]
-- ----------------------------
CREATE TABLE [paymenttype] (
[id] int NOT NULL IDENTITY(1,1) ,
[paymenttypename] varchar(100) NULL ,
[paymenttypedesc] varchar(500) NULL ,
[createdate] datetime NULL ,
[createdby] int NULL ,
[lastupdated] datetime NULL ,
[lastupdatedby] int NULL ,
[status] varchar(1) NULL 
)


GO
DBCC CHECKIDENT(N'[paymenttype]', RESEED, 19)
GO

-- ----------------------------
-- Table structure for [project]
-- ----------------------------
CREATE TABLE [project] (
[id] int NOT NULL IDENTITY(1,1) ,
[parentprojectid] int NULL ,
[masterprojectid] int NULL ,
[alphaidentifier] varchar(2) NULL ,
[costcenternumber] varchar(50) NULL ,
[projectstartdate] datetime NULL ,
[projectenddate] datetime NULL ,
[nedidentifier] varchar(50) NULL ,
[matchingfunds] numeric(15,2) NULL ,
[managementfunds] numeric(15,2) NULL ,
[approvalroundmonth] varchar(25) NULL ,
[approvalroundyear] varchar(25) NULL ,
[createdate] datetime NULL ,
[createdby] int NULL ,
[lastupdated] datetime NULL ,
[lastupdatedby] int NULL ,
[acronym] varchar(50) NULL ,
[projecttitle] varchar(100) NULL ,
[partnergrantfunds] numeric(15,2) NULL ,
[memo] varchar(5000) NULL ,
[receiptofsignedagreement] datetime NULL ,
[receiptofsignatureauthorization] datetime NULL ,
[receiptofbankinformation] datetime NULL ,
[budgetoverride] varchar(1) NULL ,
[blocktimesheet] varchar(1) NULL ,
[filelocation] varchar(512) NULL ,
[terroristcheckperformed] varchar(1) NULL ,
[solomonupdatedate] datetime NULL ,
[fieldoffice] varchar(50) NULL ,
[riskassessmentscore] numeric(18,3) NULL ,
[lowcipeadvocacy] int NULL ,
[lowproorgdevelopment] int NULL ,
[lowpartnership] int NULL ,
[themeaccesstoinfo] int NULL ,
[themebusassocdev] int NULL ,
[themecorporategovernance] int NULL ,
[themecorruption] int NULL ,
[themedemocraticgovernance] int NULL ,
[themelegalregreform] int NULL ,
[themewomenyouth] int NULL ,
[attachment] varchar(512) NULL ,
[accountingquestionnairereceived] varchar(1) NULL ,
[fy4digit] int NULL ,
[projectimpact] varchar(15) NULL ,
[receivedingrantsforapproval] datetime NULL ,
[themeinformalsectorproperty] int NULL ,
[projecttypeid] int NULL ,
[regionid] int NULL ,
[projectstatusid] int NULL ,
[donorawardid] int NULL ,
[programofficer] int NULL ,
[regionaldirector] int NULL ,
[programassistant] int NULL ,
[partnerid] int NULL ,
[countryid] int NULL ,
[organizationtypeid] int NULL ,
[newgrantee] tinyint NULL ,
[projectid] varchar(50) NULL ,
[auditsid] int NULL ,
[approved_file_location] varchar(255) NULL ,
[grant_end_date] datetime NULL ,
[grant_start_date] datetime NULL ,
[statusoverride] tinyint NULL 
)


GO
DBCC CHECKIDENT(N'[project]', RESEED, 2408)
GO

-- ----------------------------
-- Table structure for [projectkeyword]
-- ----------------------------
CREATE TABLE [projectkeyword] (
[id] int NOT NULL IDENTITY(1,1) ,
[createdby] int NULL ,
[lastupdatedby] int NULL ,
[createdate] datetime NULL ,
[lastupdated] datetime NULL ,
[keywordid] int NULL ,
[projectid] int NULL 
)


GO
DBCC CHECKIDENT(N'[projectkeyword]', RESEED, 1363)
GO

-- ----------------------------
-- Table structure for [projectmodification]
-- ----------------------------
CREATE TABLE [projectmodification] (
[id] int NOT NULL IDENTITY(1,1) ,
[createdate] datetime NULL ,
[createdby] int NULL ,
[lastupdated] datetime NULL ,
[lastupdatedby] int NULL ,
[attachment] varchar(512) NULL ,
[projectid] int NULL ,
[modificationtypeid] int NULL ,
[modifiedfrom] varchar(255) NOT NULL ,
[modifiedto] varchar(255) NOT NULL ,
[modifieddate] datetime NOT NULL ,
[modifiedby] int NOT NULL 
)


GO
DBCC CHECKIDENT(N'[projectmodification]', RESEED, 61)
GO

-- ----------------------------
-- Table structure for [projectreports]
-- ----------------------------
CREATE TABLE [projectreports] (
[id] int NOT NULL IDENTITY(1,1) ,
[reportdesc] varchar(500) NULL ,
[reportstartdate] datetime NULL ,
[reportattachment] varchar(512) NULL ,
[type] varchar(15) NULL ,
[cipegrantnumber] varchar(15) NULL ,
[acronym] varchar(50) NULL ,
[reportperiod1] datetime NULL ,
[reportperiod2] datetime NULL ,
[reportdue] datetime NULL ,
[reportreceived] datetime NULL ,
[final_str] varchar(1) NULL ,
[qtr] numeric(18,3) NULL ,
[reporttogrants] datetime NULL ,
[receivedingrants] datetime NULL ,
[notation] varchar(5000) NULL ,
[finalperiod1] datetime NULL ,
[finalperiod2] datetime NULL ,
[finaldue] datetime NULL ,
[finalreceived] datetime NULL ,
[createdate] datetime NULL ,
[createdby] int NULL ,
[lastupdated] datetime NULL ,
[lastupdatedby] int NULL ,
[attachment] varchar(512) NULL ,
[approvedbyfinance] varchar(1) NULL ,
[projectid] int NULL ,
[reportattachement] varchar(255) NULL 
)


GO
DBCC CHECKIDENT(N'[projectreports]', RESEED, 325)
GO

-- ----------------------------
-- Table structure for [projectstatus]
-- ----------------------------
CREATE TABLE [projectstatus] (
[id] int NOT NULL IDENTITY(1,1) ,
[statusname] varchar(30) NULL ,
[statusdesc] varchar(500) NULL ,
[createdate] datetime NULL ,
[createdby] int NULL ,
[lastupdated] datetime NULL ,
[lastupdatedby] int NULL 
)


GO
DBCC CHECKIDENT(N'[projectstatus]', RESEED, 6)
GO

-- ----------------------------
-- Table structure for [projectstatuschangelog]
-- ----------------------------
CREATE TABLE [projectstatuschangelog] (
[id] int NOT NULL IDENTITY(1,1) ,
[createdby] int NULL ,
[createdate] datetime NULL ,
[lastupdatedby] int NULL ,
[lastupdated] datetime NULL ,
[statuschangedate] datetime NULL ,
[newprojectstatusid] int NULL ,
[oldprojectstatusid] int NULL ,
[projectid] int NULL 
)


GO

-- ----------------------------
-- Table structure for [projectstatustransitionsallowed]
-- ----------------------------
CREATE TABLE [projectstatustransitionsallowed] (
[id] int NOT NULL IDENTITY(1,1) ,
[createdby] int NULL ,
[createdate] datetime NULL ,
[lastupdatedby] int NULL ,
[lastupdated] datetime NULL ,
[projectstatustransitiondesc] varchar(500) NULL ,
[startingfromstatus] int NULL ,
[goingtostatus] int NULL 
)


GO

-- ----------------------------
-- Table structure for [projecttype]
-- ----------------------------
CREATE TABLE [projecttype] (
[id] int NOT NULL IDENTITY(1,1) ,
[projecttype] varchar(50) NULL ,
[projectdesc] varchar(500) NULL ,
[createdate] datetime NULL ,
[createdby] int NULL ,
[lastupdated] datetime NULL ,
[lastupdatedby] int NULL 
)


GO
DBCC CHECKIDENT(N'[projecttype]', RESEED, 41)
GO

-- ----------------------------
-- Table structure for [region]
-- ----------------------------
CREATE TABLE [region] (
[id] int NOT NULL IDENTITY(1,1) ,
[regionname] varchar(100) NULL ,
[createdate] datetime NULL ,
[createdby] int NULL ,
[lastupdated] datetime NULL ,
[lastupdatedby] int NULL ,
[regionaldirector] int NULL 
)


GO
DBCC CHECKIDENT(N'[region]', RESEED, 11)
GO

-- ----------------------------
-- Table structure for [role]
-- ----------------------------
CREATE TABLE [role] (
[id] int NOT NULL IDENTITY(1,1) ,
[rolename] varchar(100) NULL ,
[createdate] datetime NULL ,
[createdby] int NULL ,
[lastupdated] datetime NULL ,
[lastupdatedby] int NULL 
)


GO

-- ----------------------------
-- Table structure for [roleprivileges]
-- ----------------------------
CREATE TABLE [roleprivileges] (
[id] int NOT NULL IDENTITY(1,1) ,
[createdate] datetime NULL ,
[createdby] int NULL ,
[lastupdated] datetime NULL ,
[lastupdatedby] int NULL ,
[roleid] int NULL ,
[link] int NULL 
)


GO

-- ----------------------------
-- Table structure for [stateprovince]
-- ----------------------------
CREATE TABLE [stateprovince] (
[id] int NOT NULL IDENTITY(1,1) ,
[stateprovincecode] varchar(25) NULL ,
[stateprovincedesc] varchar(50) NULL ,
[createdate] datetime NULL ,
[createdby] int NULL ,
[lastupdated] datetime NULL ,
[lastupdatedby] int NULL ,
[countryid] int NULL 
)


GO
DBCC CHECKIDENT(N'[stateprovince]', RESEED, 9)
GO

-- ----------------------------
-- Table structure for [useractionalertschanges]
-- ----------------------------
CREATE TABLE [useractionalertschanges] (
[id] int NOT NULL IDENTITY(1,1) ,
[markedreadorunread] varchar(1) NULL ,
[dateread] datetime NULL ,
[actionalertchangenotificationsid] int NULL ,
[readby] int NULL 
)


GO
DBCC CHECKIDENT(N'[useractionalertschanges]', RESEED, 15)
GO

-- ----------------------------
-- Table structure for [userinfo]
-- ----------------------------
CREATE TABLE [userinfo] (
[id] int NOT NULL IDENTITY(1,1) ,
[password] varchar(512) NULL ,
[loginname] varchar(50) NULL ,
[firstname] varchar(100) NULL ,
[middlename] varchar(100) NULL ,
[lastname] varchar(100) NULL ,
[email] varchar(100) NULL ,
[phone] varchar(20) NULL ,
[statuscode] varchar(30) NULL ,
[employeeid] varchar(25) NULL ,
[enddate] datetime NULL ,
[startdate] datetime NULL ,
[inactive] varchar(1) NULL ,
[requiredhours] numeric(18,3) NULL ,
[creationdate] datetime NULL ,
[createdby] int NULL ,
[lastupdated] datetime NULL ,
[lastupdatedby] int NULL ,
[fieldofficeid] int NULL ,
[regionid] int NULL ,
[usertypeid] int NULL ,
[createdate] datetime NULL ,
[preferenceid] int NULL 
)


GO
DBCC CHECKIDENT(N'[userinfo]', RESEED, 225)
GO

-- ----------------------------
-- Table structure for [userpreference]
-- ----------------------------
CREATE TABLE [userpreference] (
[id] int NOT NULL IDENTITY(1,1) ,
[aadateid] int NULL ,
[aaid] int NULL ,
[cndateid] int NULL ,
[cnid] int NULL ,
[pmasignid] int NULL ,
[pmreagionid] int NULL ,
[paymentasignid] int NULL ,
[pgasignid] int NULL ,
[pgreagionid] int NULL ,
[aareagionid] int NULL ,
[aaasignid] int NULL ,
[cnreagionid] int NULL ,
[cnasignid] int NULL 
)


GO
DBCC CHECKIDENT(N'[userpreference]', RESEED, 11)
GO

-- ----------------------------
-- Table structure for [userrole]
-- ----------------------------
CREATE TABLE [userrole] (
[id] int NOT NULL IDENTITY(1,1) ,
[createdate] datetime NULL ,
[createdby] int NULL ,
[lastupdated] datetime NULL ,
[lastupdatedby] int NULL ,
[userinfoid] int NULL ,
[roleid] int NULL 
)


GO

-- ----------------------------
-- Table structure for [usertype]
-- ----------------------------
CREATE TABLE [usertype] (
[id] int NOT NULL IDENTITY(1,1) ,
[usertypename] varchar(100) NULL ,
[createdate] datetime NULL ,
[createdby] int NULL ,
[lastupdated] datetime NULL ,
[lastupdatedby] int NULL 
)


GO

-- ----------------------------
-- View structure for [vw_paymentrequest]
-- ----------------------------
CREATE VIEW [vw_paymentrequest] AS 
SELECT lineitem.projectid AS projectid, lineitem.paymentrequestid AS paymentrequestid, SUM(lineitem.paymentrequestamount) AS totalamount
FROM paymentrequestlineitem lineitem
LEFT JOIN paymentrequest request ON request.id = lineitem.paymentrequestid
GROUP BY lineitem.projectid, lineitem.paymentrequestid
GO

-- ----------------------------
-- View structure for [vw_projectpayment]
-- ----------------------------
CREATE VIEW [vw_projectpayment] AS 
SELECT
project.id,
project.projectid AS projectid,
project.projecttitle AS title,
project.costcenternumber AS costcenternumber,
projecttype.projecttype AS projecttypedesc,
projectstatus.statusname AS status,
region.regionname AS region,
country.countryname AS country,
project.acronym AS acronym,
project.projectstartdate AS projectstartdate,
project.projectenddate AS projectenddate,
partner.partnername AS partnername,
project.budgetoverride AS budgetoverride,
project.statusoverride AS statusoverride,
(ISNULL(project.managementfunds,0) + ISNULL(project.partnergrantfunds, 0)) AS totalfunds,
((ISNULL(project.managementfunds,0) + ISNULL(project.partnergrantfunds, 0)) 
	- ISNULL((SELECT SUM(paymentrequestamount) FROM paymentrequestlineitem payment WHERE payment.projectid = project.id), 0)) AS remainingfunds
FROM
project project
LEFT JOIN projecttype projecttype ON projecttype.id = project.projecttypeid
LEFT JOIN projectstatus projectstatus ON projectstatus.id = project.projectstatusid
LEFT JOIN region region ON region.id = project.regionid
LEFT JOIN country country ON country.id = project.countryid
LEFT JOIN partner partner ON partner.id = project.partnerid
GO

-- ----------------------------
-- Procedure structure for [AllProcedureRelatedToOneDay]
-- ----------------------------


CREATE PROCEDURE [AllProcedureRelatedToOneDay]
	
AS
BEGIN
EXECUTE [dbo].[Donor_Awards];
EXECUTE [dbo]. [Projects];
EXECUTE [dbo].Projects_Penidng_active;
EXECUTE [dbo].Projects_Active;
END;





GO

-- ----------------------------
-- Procedure structure for [Donor_Awards]
-- ----------------------------


-- =============================================
-- Author:		<Krushna>
-- Create date: <2/11/2010>
-- Description:	When the Project ‘End Date’ exceeds the current date the ‘status’ 
-- should be changed to “Over”IF the current status = “Active”.
-- =============================================
CREATE PROCEDURE [Donor_Awards]
	-- Add the parameters for the stored procedure here
AS
BEGIN
	DECLARE @statusidActive int;
	DECLARE @statusidOver int ;
	SELECT @statusidActive=ID from donorawardstatus where statusname='Active';
	SELECT @statusidOver=ID from donorawardstatus where statusname='Over';
	update donoraward set donorawardstatusid=@statusidOver where [donorawardstatusid]=@statusidActive and  getdate()>enddate;
	
END


GO

-- ----------------------------
-- Procedure structure for [getDashboard_count]
-- ----------------------------


CREATE PROCEDURE [getDashboard_count]
 AS
BEGIN
	DECLARE @statusidActive int;
	DECLARE @statusidPending int ;
	DECLARE @projectTypeIdPartener int;
	DECLARE @statusidOver int ;
	
	DECLARE @projectActiveCount int ;
	DECLARE @projectOverCount int ;
	DECLARE @projectOver30Count int ;
	DECLARE @projectNoPayment60 int ;
	Declare @paymentrequestamount numeric(15,2);
	 
	SELECT @statusidActive=ID from projectstatus where statusname='Active';
	SELECT @statusidPending=ID from projectstatus where statusname='Pending';
	SELECT @statusidOver=ID from projectstatus where statusname='Over';
	
	SELECT @projectTypeIdPartener=ID from [projecttype] where [projecttype]='Partner';
	
	select @projectActiveCount=count(*) from project where projectstatusid=@statusidActive and projecttypeid!=@projectTypeIdPartener;
	select @projectOverCount=count(*) from project where projectstatusid=@statusidOver and projecttypeid!=@projectTypeIdPartener;
	
	---DB3 Projects "Over" within 30 days	Project – Find	Project.status = “Active” and Project.type <> “Partner Grant” 
	--and project.end_date <= today()+30
	
	SELECT @projectOver30Count=Count(project.id) 
	FROM (project INNER JOIN projecttype ON project.projecttypeid = projecttype.id) INNER JOIN projectstatus ON project.projectstatusid = projectstatus.id
	WHERE ((Not (projecttype.projecttype)='partner') AND ((projectstatus.statusname)='active') AND ((project.projectenddate)>=getDate() And (project.projectenddate)<=getDate()+30));

	--select @projectOver30Count=count(*) from project where projectstatusid=@statusidActive and projecttypeid!=@projectTypeIdPartener and DATEDIFF(DAY,projectenddate,GETDATE())<=30;
	--New Projects where no Payment Activity >= 60 days	Project – 
	--Find	Project.status = “Active” and Project.type <> “Partner Grant” 
	--and project.start_date <= today()+60 and no payment request records related to this project have been created.
	select @projectNoPayment60=count(*) from project where projectstatusid=@statusidActive and projecttypeid!=@projectTypeIdPartener 
	and DATEDIFF(DAY,GETDATE(),projectstartdate)<=60 and
	0=(select count(*) from paymentrequestlineitem where projectid=project.id );
	
	update dashboardlink set count=@projectActiveCount where id=1;
	update dashboardlink set count=(@projectOverCount) where id=2;
	update dashboardlink set count =(@projectOver30Count) where id=3;
	update dashboardlink set count=(@projectNoPayment60) where id=4;
	
	---Active" Projects with more than 90% Budget Disbursed	Project – 
	--Find	Project.status = “Active” and Project.type <> “Partner Grant” and 
	--the sum total of all related payment requests divided by (/) project.management_funds >= 90%--
	
	begin try
	
	UPDATE dashboardlink set count=
	(select count(*) from project where projectstatusid=@statusidActive and projecttypeid!=@projectTypeIdPartener 
	and project.managementfunds!=0 and
	(select SUM(paymentrequestamount) from paymentrequestlineitem 
	where projectid=project.id)/project.managementfunds>=0.9) WHERE id=5;
	end try
	begin catch
	print 'dived by zero'
	end catch
	
	---Project status active and project type partner grant ---
	update dashboardlink set count=(select count(*) from project where projectstatusid=@statusidActive and projecttypeid=@projectTypeIdPartener) where id=6;
	-- Partner Grant – Find	Project.status = “Over” and Project.type = “Partner Grant”---
	update dashboardlink set count=(select count(*) from project where projectstatusid=@statusidOver and projecttypeid=@projectTypeIdPartener) where id=7;
	--Project.status = “Active” and Project.type = “Partner Grant” and project.start_date >= today() and project. ReceiptOfSignedAgreement IS NULL--
	update dashboardlink set count=
	(SELECT Count(project.id) AS CountOfid
	FROM (project INNER JOIN projecttype ON project.projecttypeid = projecttype.id) INNER JOIN projectstatus ON project.projectstatusid = projectstatus.id
	WHERE (((projecttype.projecttype)='partner') AND ((projectstatus.statusname)='active') AND ((project.projectstartdate)<getDate()) AND ((project.receiptofsignedagreement) Is Null))
	) where id=8;
	 
	-- New Partner Grants with no Payment Activity >= 60 days	Partner Grant – 
	--Find	Project.status = “Active” and Project.type = “Partner Grant” and project.start_date <= today()+60 
	--and no payment request records related to this project have been created-- 
	update dashboardlink set count=
	(select count(*) from project where projectstatusid=@statusidActive and projecttypeid=@projectTypeIdPartener and
	DATEDIFF(DAY,GETDATE(),projectstartdate)<=60 and 
	0=(select count(*) from paymentrequestlineitem where projectid=project.id )) where id=9
	
	--- Active" Partner Grants with more than 90% Budget Disbursed	Partner Grant 
	-- Find	Project.status = “Active” and Project.type = “Partner Grant” 
	--and the sum total of all related payment requests divided by (/) project.management_funds >= 90%---
	
	begin try
	UPDATE dashboardlink set count=
	(select count(*) from project where projectstatusid=@statusidActive and projecttypeid=@projectTypeIdPartener 
	and project.managementfunds!=0
	and
	(select SUM(paymentrequestamount) from paymentrequestlineitem where projectid=project.id)/project.managementfunds>=0.9) WHERE id=10;
	 
	 end try
	 begin catch
	 print 'divided by zero exception';
	 end catch
	 
	-- Payment Request--
	--- - # of Open Payment Requests	Payment Request – 
	--Find	Paymentrequest.status <> “Cancelled” or “Paid”---
	update dashboardlink set count= 
	(select count(*) from paymentrequest where statuscode!='Cancelled' and statuscode!='Paid') where id=11
	
	--Payment Requests that have been open for greater than 30 days	Payment Request – 
	--Find	Paymentrequest.status <> “Cancelled” or “Paid” and paymentrequest.createdate <= Today() – 30--
	update dashboardlink set count= 
	(select count(*) from paymentrequest where statuscode!='Cancelled' and statuscode!='Paid' 
	and datediff(day,GETDATE(),createdate)<=-30) where id=12
	--- Paid Payment Requests, past 30 days	Payment Request – 
	--Find	Paymentrequest.status = “Paid” and paymentrequest.datepaid >= Today()– 30---
	update dashboardlink set count= 
	(SELECT count(*)
	FROM paymentrequest LEFT JOIN paymenttype ON paymentrequest.paymenttypeid = paymenttype.id
	WHERE (((paymentrequest.statuscode)='paid') AND ((paymentrequest.datepaid) Between getDate()-30 And getDate()))
	) where id=13
	---Rejected Payment Requests Payment Request – Find	Paymentrequest.status <> “Rejected”---
	update dashboardlink set count= 
	(SELECT Count(paymentrequest.id) AS CountOfid
	FROM paymentrequest LEFT JOIN paymenttype ON paymentrequest.paymenttypeid = paymenttype.id
	WHERE (((paymentrequest.statuscode)='rejected'))) where id=14
	---Reports ---
	--NarrativeReports due within 30 days --
	--Partner Grant – Find	Type' = "Narrative" and 'Report Due' <= Today()+30 and 'Report Received' Is Null.--
	update dashboardlink set count= 
	(select count(*) from projectreports pr where type='Narrative' and reportreceived is null and  datediff(day,GETDATE(),reportdue)<=30 and
	pr.projectid in(select id from project where projecttypeid=@projectTypeIdPartener))  where id=15;
	--- Financial Reports due within 30 days	Partner Grant – Find	
	--Type' = "Financial" and 'Report Due' <= Today()+30 and 'Report Received' Is Null.---
	update dashboardlink set count= 
	(select count(*) from projectreports pr where type='Financial' and reportreceived is null and  datediff(day,GETDATE(),reportdue)<=30 and
	pr.projectid in(select id from project where projecttypeid=@projectTypeIdPartener))  where id=16;
	--- Narrative Reports Past Due	Partner Grant – Find	
	--Type' = "Narrative" and 'Report Due' <Today() and 'Report Received' Is Null.---
	update dashboardlink set count= 
	(select count(*) from projectreports pr where type='Narrative' and reportreceived is null and  datediff(day,GETDATE(),reportdue)<0 and
	pr.projectid in(select id from project where projecttypeid=@projectTypeIdPartener))  where id=17;
	---Financial Reports Past Due	Partner Grant – Find	
	--Type' = "Financial" and 'Report Due' <Today() and 'Report Received'  ---
	update dashboardlink set count= 
	(select count(*) from projectreports pr where type='Financial' and reportreceived is null and  datediff(day,GETDATE(),reportdue)<0 and
	pr.projectid in(select id from project where projecttypeid=@projectTypeIdPartener))  where id=18;
	--- IMPs due within 30 days	Partner Grant – Find	
	--IMP Due Date' <=Today()+30 and 'IMP Performed' Is Null---
	update dashboardlink set count= 
	(SELECT count(*)
	FROM imp
		WHERE imp.impduedate Between getDate() And getDate()+30 AND imp.impperformed Is Null and projectid  in
		(select id from project where projecttypeid=@projectTypeIdPartener)
	) where id=19;
	--- DB20 - IMP’s Past Due	Partner Grant – Find	IMP Due Date' <Today() and 'IMP Performed' Is Null.---
	update dashboardlink set count= 
	(select count(*) from imp where datediff(day,GETDATE(),impduedate)<0 and impperformed is null and 
	projectid in(select id from project where projecttypeid=@projectTypeIdPartener)) where id=20;
	---DB21 - Auditor Analysis & Selection Sheets (AASS) due within 30 days	Partner Grant – Find	
	--FA Selection Memo Due Date' < Today()+30 and 'FA Selection Memo Received' Is Null ---
	update dashboardlink set count= 
	(SELECT count(*)
	FROM audits
	WHERE audits.faselectionmemoduedate Between getDate() And getDate()+30 AND audits.faselectionmemoreceived Is Null and projectid in
	(select id from project where projecttypeid=@projectTypeIdPartener)
	) where id=21
	
	---DB22 - Auditor Analysis & Selection Sheets (AASS) Past Due	Partner Grant – Find	
	--FA Selection Memo Due Date' < Today() and 'FA Selection Memo Received' Is Null ---
	update dashboardlink set count= 
	(select count(*) from audits where datediff(day,GETDATE(),faselectionmemoduedate)<0 and faselectionmemoreceived is null and projectid in
	(select id from project where projecttypeid=@projectTypeIdPartener)) where id=22
	--forget insert id 23 in dashboard link--
	--- DB23 - Interim Audits Due within 30 days	
	--Partner Grant – Find	IA Due Date' <= Today()+30 and 'IA Received' Is Null---
	update dashboardlink set count= 
	(select count(*) from audits where audits.iadue Between getDate() And getDate()+30 and iareceived is null and projectid in
	(select id from project where projecttypeid=@projectTypeIdPartener)) where id=24;
	---	DB24 - Final Audits Due within 30 days	Partner Grant – Find	
	---Date Final Audit Due' <= Today()+30 and 'Date Final Audit Received' Is Null.---
	update dashboardlink set count= 
	(SELECT count(*)
	FROM (audits INNER JOIN project ON audits.projectid = project.id) INNER JOIN projecttype ON project.projecttypeid = projecttype.id
	WHERE (((audits.fadue) Between getDate() And getDate()+30) AND ((audits.fareceived) Is Null) AND ((projecttype.projecttype)='partner'))
	) where id=25;
	---DB25 - Interim Audit Date Past Due	Partner Grant – Find	
	--IA Due Date' < Today() and 'IA Received' Is Null---
	update dashboardlink set count= 
	(SELECT count(*)
	FROM (audits INNER JOIN project ON audits.projectid = project.id) INNER JOIN projecttype ON project.projecttypeid = projecttype.id
	WHERE (((audits.iadue)<getDate()) AND ((audits.iareceived) Is Null) AND ((projecttype.projecttype)='partner')))where id=26;
	
	--- DB26 - Final Audit Date Past Due	Partner Grant – Find	
	--Date Final Audit Due' < Today() and 'Date Final Audit Received' Is Null.	---
	update dashboardlink set count= 
	(SELECT count(*)
	FROM (audits INNER JOIN project ON audits.projectid = project.id) INNER JOIN projecttype ON project.projecttypeid = projecttype.id
	WHERE (((audits.fadue)<getDate()) AND ((audits.fareceived) Is Null) AND ((projecttype.projecttype)='partner'))
	) where id=27;
	--- End of Reports---
	---Donor Awards---
	--DB27 - New Donor Awards (less than 30 days old)	Donor Award – Find	Start Date' <= Today()+30
	update dashboardlink set count=(SELECT count(*)
	FROM donoraward LEFT JOIN donorawardstatus ON donoraward.donorawardstatusid = donorawardstatus.id
	WHERE (((donoraward.startdate)>=getDate()-30 And (donoraward.startdate)<=getDate()) AND ((donorawardstatus.statusname)='active'))) where id=28;
	--DB28 - Donor Awards "Over" in < 90 days	Donor Award – Find	End Date' <= Today()+90
	update dashboardlink set count=(SELECT count(*)
	FROM donoraward LEFT JOIN donorawardstatus ON donoraward.donorawardstatusid = donorawardstatus.id
	WHERE (((donoraward.enddate) Between getDate() And getDate()+90) AND ((donorawardstatus.statusname)='active'))) where id=29;
	---End of Donor Awards---
	END

	
	
/****** Object:  UserDefinedFunction [dbo].[getProjectList]    Script Date: 03/24/2012 00:17:25 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[getProjectList]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[getProjectList]

GO

-- ----------------------------
-- Procedure structure for [Projects]
-- ----------------------------


CREATE PROCEDURE [Projects]
	-- Add the parameters for the stored procedure here
--When the Project ‘End Date’ exceeds the current date the ‘status’ 
--should be changed to “Over”IF the current status = “Active”
AS
BEGIN
	DECLARE @statusidActive int;
	DECLARE @statusidOver int ;
	SELECT @statusidActive=ID from projectstatus where statusname='Active';
	SELECT @statusidOver=ID from projectstatus where statusname='Over';
	update  project  set [projectstatusid]=@statusidOver where 
	[projectstatusid]=@statusidActive  and getdate()>projectenddate;
	
END


GO

-- ----------------------------
-- Procedure structure for [Projects_Active]
-- ----------------------------


-- =============================================
-- Author:		<Krushna>
-- Create date: <2/11/2010>
-- When the Grant ‘start date’ exceeds the current date, If the ‘status’ = “Pending” 
--AND the ‘type’ = “Partner Grant”, the ‘status’ should be changed to “Active” IF also 
--1) The Risk Assessment score of the related audit record has been entered; 
--2) The Accounting Questionairre has been marked as received; 
--3) The Signed Agreement Received date has been entered; 
--4) The Bank Information Received date has been entered; 
--5) The Terrorist Check Performed has been marked as completed.)
-- =============================================
CREATE PROCEDURE [Projects_Active]
	-- Add the parameters for the stored procedure here
	
AS
BEGIN
	
	DECLARE @statusidActive int;
	DECLARE @statusidPending int ;
	DECLARE @projectTypeIdPartener int;
	 
	SELECT @statusidActive=ID from projectstatus where statusname='Active';
	SELECT @statusidPending=ID from projectstatus where statusname='Pending';
	SELECT @projectTypeIdPartener=ID from [projecttype] where [projecttype]='Partner';
	
	update project set [projectstatusid]=@statusidActive  where [projectstatusid]=@statusidPending 
	and projecttypeid=@projectTypeIdPartener and   getDate()>projectstartdate  and riskassessmentscore is not null and receiptofsignedagreement is not null 
	and receiptofbankinformation is not null and terroristcheckperformed='-1' and accountingquestionnairereceived='-1';
	
END


GO

-- ----------------------------
-- Procedure structure for [Projects_Penidng_active]
-- ----------------------------

--When the Project ‘start date’ exceeds the current date and the 
--‘status’ = “Pending” and the ‘type’ does NOT equal “Partner Grant” 
--the status should be changed to “Active”.

CREATE PROCEDURE [Projects_Penidng_active]
AS
BEGIN
	
	DECLARE @statusidActive int;
	DECLARE @statusidPending int ;
	DECLARE @typeid int ;
	SELECT @statusidActive=ID from projectstatus where statusname='Active';
	SELECT @statusidPending=ID from projectstatus where statusname='Pending';
	SELECT @typeid=ID from [projecttype] where [projecttype]='Partner'
	
	update  project set [projectstatusid]=@statusidActive where 
	[projectstatusid]=@statusidPending and projecttypeid!=@typeid and getdate()>projectstartdate;
END

GO

-- ----------------------------
-- Procedure structure for [updateActionalableMessage]
-- ----------------------------


CREATE PROCEDURE [updateActionalableMessage]
AS
BEGIN

begin try
truncate table actionablealertsmessage;
end try
	begin catch
	print 'Error in truncate the Table';
	end catch

---AA1AA1 - AASS Overdue	Partner Monitoring – View	
--‘FA Selection Memo Due Date’ > Today() and ‘FA Selection Memo Received’ Is Null and Project ‘Type’ = “Partner Grant”.	
--“The AASS from Partner <Partner Name> for Grant # <project id> was due on <FA Selection Memo Due Date> 
--but has not been received. ---
	DECLARE @statusidActive int;
	DECLARE @statusidPending int ;
	DECLARE @projectTypeIdPartener int;
	Declare @partnername varchar(255);
	Declare @dudate datetime;
	DECLARE @projectid int;
	DECLARE @id int;
	DECLARE @uid1 int;
	DECLARE @uid2 int;
	DECLARE @uid3 int;
	DECLARE @regionid int;
	
	DECLARE @sprojectid varchar(50);
	DECLARE @message varchar(500);
	DECLARE @displaydate datetime;
	SELECT @statusidActive=ID from projectstatus where statusname='Active';
	SELECT @statusidPending=ID from projectstatus where statusname='Pending';
	SELECT @projectTypeIdPartener=ID from [projecttype] where [projecttype]='Partner';
	
	
	DECLARE AA1_cur  cursor for SELECT project.id,project.projectid,audits.faselectionmemoduedate,project.programofficer,project.regionaldirector,
	project.programassistant,project.regionid
	FROM (project INNER JOIN projecttype ON project.projecttypeid = projecttype.id) INNER JOIN audits ON project.id = audits.projectid
	WHERE (((projecttype.projecttype)='partner') AND ((audits.faselectionmemoduedate) Is Not Null And (audits.faselectionmemoduedate)<getDate()) AND ((audits.faselectionmemoreceived) Is Null));

	open AA1_cur
	fetch next from AA1_cur into @projectid,@sprojectid,@displaydate,@uid1,@uid2,@uid3,@regionid;
	while @@FETCH_STATUS=0
	begin
	select @partnername= partnername from partner where id=(select partnerid from project where id=@projectid);
	--select @dudate=faselectionmemoduedate from audits where projectid=@projectid;
	set @message='The AASS from Partner '+@partnername+' for Grant # '+@sprojectid+' was due on '+convert(varchar,@displaydate,1)+' but has not been received.';
	
	insert into actionablealertsmessage(actionablealertsid,message,date,url,programofficer,regionaldirector,programassistant,regionid) 
	values(1,@message,@displaydate+1,'showEditPartnergrantView.do?id='+cast(@projectid as varchar(20)),@uid1,@uid2,@uid3,@regionid);
	
	fetch next from AA1_cur into @projectid,@sprojectid,@displaydate,@uid1,@uid2,@uid3,@regionid; 
	END;
	close AA1_cur;
	deallocate AA1_cur;
	---AA2 - Final Audit Past Due	Partner Monitoring – View	
	--Date Final Audit Due' > Today() and 'Date Final Audit Received' Is Null and Project ‘Type’ = “Partner Grant”	
	--The Final Audit from Partner <Partner Name> for Grant # <project id> was due on <Date Final Audit Due> 
	--but has not been received.
	--datediff(day,GETDATE(),fadue) menas fadue-getdate()
	
	DECLARE AA2_cur  cursor for SELECT project.id,project.projectid,audits.fadue,project.programofficer,project.regionaldirector,
	project.programassistant,project.regionid
	FROM (project INNER JOIN projecttype ON project.projecttypeid = projecttype.id) INNER JOIN audits ON project.id = audits.projectid
	WHERE (((projecttype.projecttype)='partner') AND ((audits.fadue)<getDate()) AND ((audits.fareceived) Is Null));

	open AA2_cur
	fetch next from AA2_cur into @projectid,@sprojectid,@displaydate,@uid1,@uid2,@uid3,@regionid;
	while @@FETCH_STATUS=0
	begin
	select @partnername= partnername from partner where id=(select partnerid from project where id=@projectid);
	select @dudate=fadue from audits where projectid=@projectid;
	set @message='The Final Audit from Partner '+@partnername+' for Grant #'+@sprojectid+' was due on '+convert(varchar,@dudate,1);
	insert into actionablealertsmessage(actionablealertsid,message,date,url,programofficer,regionaldirector,programassistant,regionid)
	values(2,@message,@displaydate+1,'showEditPartnergrantView.do?id='+cast(@projectid as varchar(20)),@uid1,@uid2,@uid3,@regionid);
	fetch next from AA2_cur into @projectid,@sprojectid,@displaydate,@uid1,@uid2,@uid3,@regionid 
	END;
	close AA2_cur;
	deallocate AA2_cur;
	--AA3 - Financial Report Past Due	Partner Grant – View	
	--Type' = "Financial" and 'Report Due' > Today() and 'Report Received' Is Null 
	--and Project ‘Type’ = “Partner Grant”.	
	--A Financial Report from Partner <Partner Name> for Grant # <project id> was due on <Report Due> but has not been received
	
	DECLARE AA3_cur  cursor for SELECT projectreports.id,project.id,project.projectid,projectreports.reportdue,project.programofficer,project.regionaldirector,
	project.programassistant,project.regionid
	FROM (projectreports INNER JOIN project ON projectreports.projectid = project.id) INNER JOIN projecttype ON project.projecttypeid = projecttype.id
	WHERE (((projectreports.type)='financial') AND ((projectreports.reportdue)<getDate()) AND ((projecttype.projecttype)='partner') AND ((projectreports.reportreceived) Is Null));

	Declare @otherid int;
	open AA3_cur
	fetch next from AA3_cur into @id,@otherid,@sprojectid,@displaydate,@uid1,@uid2,@uid3,@regionid;
	while @@FETCH_STATUS=0
	begin
	select @projectid=projectid from projectreports where id=@id;
	select @partnername= partnername from partner where id=(select partnerid from project where id=@projectid);
	select @dudate=reportdue from projectreports where id=@id;
	set @message='A Financial Report from Partner '+@partnername+' for Grant # '+@sprojectid+' was due on '+convert(varchar,@dudate,1)+' but has not been received';
	insert into actionablealertsmessage(actionablealertsid,message,date,url,programofficer,regionaldirector,programassistant,regionid)
	values(3,@message,@displaydate+1,'showEditPartnergrantView.do?id='+cast(@otherid as varchar(20)),@uid1,@uid2,@uid3,@regionid);
	fetch next from AA3_cur into @id,@otherid,@sprojectid,@displaydate,@uid1,@uid2,@uid3,@regionid; 
	END;
	close AA3_cur;
	deallocate AA3_cur;
	
	--AA4 - Interim Audit Past Due	Partner Monitoring – View	
	--IA Due Date' > Today() and 'Date Final Audit Received' Is Null and Project ‘Type’ = “Partner Grant”.	
	--An Interim Audit from Partner <Partner Name for Grant # <project id> was due on <IA Due Date> 
	--but has not been received.
	DECLARE AA4_cur  cursor for SELECT project.id,project.projectid,audits.iadue,project.programofficer,project.regionaldirector,
	project.programassistant,project.regionid
	FROM (project INNER JOIN projecttype ON project.projecttypeid = projecttype.id) INNER JOIN audits ON project.id = audits.projectid
	WHERE (((projecttype.projecttype)='partner') AND ((audits.iadue)<getDate()) AND ((audits.fareceived) Is Null));

	open AA4_cur
	fetch next from AA4_cur into @projectid,@sprojectid,@displaydate,@uid1,@uid2,@uid3,@regionid;
	while @@FETCH_STATUS=0
	begin
	select @partnername= partnername from partner where id=(select partnerid from project where id=@projectid);
	select @dudate=iadue from audits where projectid=@projectid;
	set @message='An Interim Audit from Partner '+@partnername+' for Grant #'+@sprojectid+' was due on '+convert(varchar,@dudate,1)+' but has not been received';
	insert into actionablealertsmessage(actionablealertsid,message,date,url,programofficer,regionaldirector,programassistant,regionid) 
	values(4,@message,@displaydate+1,'showEditPartnergrantView.do?id='+cast(@projectid as varchar(20)),@uid1,@uid2,@uid3,@regionid);
	fetch next from AA4_cur into @projectid ,@sprojectid,@displaydate,@uid1,@uid2,@uid3,@regionid;
	END;
	close AA4_cur;
	deallocate AA4_cur;
	--- Narrative Report Past Due	Partner Grant – View	
	--Type' = "Narrative" and 'Report Due' > Today() and 'Report Received' Is Null 
	--and Project ‘Type’ = “Partner Grant”	
	--A Narrative Report from Partner <Partner Name for Grant # <project id> was due on <Report Due> but has not been received
	
	DECLARE AA5_cur  cursor for SELECT projectreports. id,project.id,project.projectid,projectreports.reportdue,project.programofficer,project.regionaldirector,
	project.programassistant,project.regionid 
	FROM (projectreports INNER JOIN project ON projectreports.projectid = project.id) 
	INNER JOIN projecttype ON project.projecttypeid = projecttype.id
	WHERE (((projectreports.type)='narrative') AND ((projectreports.reportdue)<getDate()) 
	AND ((projecttype.projecttype)='partner') AND ((projectreports.reportreceived) Is Null));

	open AA5_cur
	fetch next from AA5_cur into @id,@otherid,@sprojectid,@displaydate,@uid1,@uid2,@uid3,@regionid;
	while @@FETCH_STATUS=0
	begin
	select @projectid=projectid from projectreports where id=@id;
	select @partnername= partnername from partner where id=(select partnerid from project where id=@projectid);
	select @dudate=reportdue from projectreports where id=@id;
	set @message='A Narrative Report from Partner '+@partnername+' for Grant # '+@sprojectid+' was due on '+convert(varchar,@dudate,1)+' but has not been received';
	insert into actionablealertsmessage(actionablealertsid,message,date,url,programofficer,regionaldirector,programassistant,regionid) 
	values(5,@message,@displaydate+1,'showEditPartnergrantView.do?id='+cast(@otherid as varchar(20)),@uid1,@uid2,@uid3,@regionid);
	fetch next from AA5_cur into @id,@otherid,@sprojectid ,@displaydate,@uid1,@uid2,@uid3,@regionid;
	END;
	close AA5_cur;
	deallocate AA5_cur;
	---AA6 - Past Due IMP	Partner Monitoring - View	
	--IMP Due Date' >Today() and 'IMP Performed' Is Null and Project ‘Type’ = “Partner Grant”	
	--An IMP from Partner <Partner Name for Grant # <project id> was due on <IMP Due Date> but has not been received.
	DECLARE AA6_cur  cursor for SELECT imp.id,project.id,project.projectid,imp.impduedate,project.programofficer,project.regionaldirector,
	project.programassistant,project.regionid
	FROM (imp INNER JOIN project ON imp.projectid = project.id) INNER JOIN projecttype ON project.projecttypeid = projecttype.id
	WHERE (((imp.impduedate)<getDate()) AND ((projecttype.projecttype)='partner') AND ((imp.impperformed) Is Null));
	
	open AA6_cur
	fetch next from AA6_cur into @id,@otherid,@sprojectid,@displaydate,@uid1,@uid2,@uid3,@regionid;
	while @@FETCH_STATUS=0
	begin
	select @projectid=projectid from imp where id=@id;
	select @partnername= partnername from partner where id=(select partnerid from project where id=@projectid);
	select @dudate=impduedate from imp where id=@id;
	set @message='An IMP from Partner '+@partnername+' for Grant #'+@sprojectid+' was due on '+convert(varchar,@dudate,1)+' but has not been received';
	insert into actionablealertsmessage(actionablealertsid,message,date,url,programofficer,regionaldirector,programassistant,regionid) 
	values(6,@message,@displaydate+1,'showEditPartnergrantView.do?id='+cast(@otherid as varchar(20)),@uid1,@uid2,@uid3,@regionid);
	fetch next from AA6_cur into @id,@otherid,@sprojectid,@displaydate,@uid1,@uid2,@uid3,@regionid; 
	END;
	close AA6_cur;
	deallocate AA6_cur;
	--AA7 - Overdue Payment Request	Payment Request – View	
	--createdate' <=today()+30 and 'status' <> "paid" or "cancelled".	
	--Payment Request #<request_id> created on <createdate> for payee <payee name> has been open and unpaid for over 30 days.
	DECLARE AA7_cur  cursor for SELECT paymentrequest.id,paymentrequest.createdate,paymentrequest.createdby
	FROM paymentrequest
	WHERE (((paymentrequest.createdate)<=getDate()-30) 
	AND (Not (paymentrequest.statuscode)='paid' And 
	Not (paymentrequest.statuscode)='cancelled'));

	DECLARE @payeeName varchar(255);
	DECLARE @payeeid int;
	open AA7_cur
	fetch next from AA7_cur into @id,@displaydate,@uid1;
	while @@FETCH_STATUS=0
	begin
	select @payeeid=payeeid from paymentrequest where id=@id;
	select @payeeName=payeename,@dudate=payee.createdate from payee where id=@payeeid;
	set @message='Payment Request #'+cast(@id as varchar(20))+' created on '+convert(varchar,@displaydate,1)+' for payee '+@payeeName+' has been open and unpaid for over 30 days. ';
	insert into actionablealertsmessage(actionablealertsid,message,date,url,programofficer,regionaldirector,programassistant) 
	values(7,@message,@displaydate+30,'showEditPaymentrequest.do?id='+cast(@id as varchar(20)),@uid1,@uid1,@uid1);
	fetch next from AA7_cur into @id,@displaydate,@uid1 
	END;
	close AA7_cur;
	deallocate AA7_cur;
	--Rejected Payment Request	Payment Request – View	
	--‘status’ = “Rejected”	
	--Payment Request #<request_id> created on <createdate> for payee <payee name> was rejected on <LastUpdated>
	DECLARE AA8_cur  cursor for select id,paymentrequest.createdby from paymentrequest  where  statuscode='Rejected' ;
	DECLARE @lastupdate datetime;
	open AA8_cur
	fetch next from AA8_cur into @id,@uid1;
	while @@FETCH_STATUS=0
	begin
	select @payeeid=payeeid from paymentrequest where id=@id;
	select @payeeName=payeename,@dudate=payee.createdate,@lastupdate=lastupdated from payee where id=@payeeid;
	
	set @message='Payment Request #'+cast(@id as varchar(20))+' created on '+convert(varchar,@dudate,1)+' for payee '+@payeeName+' was rejected on '+convert(varchar,@lastupdate,1);
	insert into actionablealertsmessage(actionablealertsid,message,date,url,programofficer,regionaldirector,programassistant)
	values(8,@message,@lastupdate,'showEditPaymentrequest.do?id='+cast(@id as varchar(20)),@uid1,@uid1,@uid1);
	fetch next from AA8_cur into @id,@uid1 
	END;
	close AA8_cur;
	deallocate AA8_cur;
	--Actionable Alert	AA9 - No Partner Grant Payment Request Activity	Partner Grant – View	
	--type' = "Partner Grant", 'Start Date' < Today() + 60 and there are no related payment 
	--requests associated with this Grant.
	--Grant # <project id> for Partner <Partner Name> became active over 60 days ago on <start date> 
	--and there has been no Payment Request activity against it.
	DECLARE AA9_cur  cursor for select id,projectstartdate,projectid,project.programofficer,project.regionaldirector,
	project.programassistant,project.regionid  
	from project  where  projecttypeid=@projectTypeIdPartener and  projectstatusid=@statusidActive and
	projectstartdate<GETDATE()-60 and 0=(select count(*) from paymentrequestlineitem where projectid=project.id)
	
	open AA9_cur
	fetch next from AA9_cur into @projectid,@dudate,@sprojectid,@uid1,@uid2,@uid3,@regionid;
	while @@FETCH_STATUS=0
	begin
	select @partnername= partnername from partner where id=(select partnerid from project where id=@projectid);
	
	set @message='Grant #'+@sprojectid+' for Partner '+@partnername+ ' became active over 60 days ago on '+convert(varchar,@dudate,1)+
	' and there has been no Payment Request activity against it';
	insert into actionablealertsmessage(actionablealertsid,message,date,url,programofficer,regionaldirector,programassistant,regionid) 
	values(9,@message,@dudate+60,'showEditPartnergrantView.do?id='+cast(@projectid as varchar(20)),@uid1,@uid2,@uid3,@regionid);
	fetch next from AA9_cur into @projectid,@dudate,@sprojectid,@uid1,@uid2,@uid3,@regionid; 
	END;
	close AA9_cur;
	deallocate AA9_cur;
	--Actionable Alert	AA10 - No Project Payment Request Activity	Project – Summary	
	--type' <> "Partner Grant", 'Start Date' < Today() + 60 
	--and there are no related payment requests associated with this Project.	
	--Project # <project id> became active over 60 days ago on <start date> and there has been no Payment Request activity against it
	DECLARE AA10_cur  cursor for select id,projectstartdate,projectid,project.programofficer,project.regionaldirector,
	project.programassistant,project.regionid  
	from project  where  projecttypeid!=@projectTypeIdPartener and projectstatusid=@statusidActive and
	datediff(day,projectstartdate,getdate())>60 and 0=(select count(*) from paymentrequestlineitem where projectid=project.id)
	open AA10_cur
	fetch next from AA10_cur into @projectid,@dudate,@sprojectid,@uid1,@uid2,@uid3,@regionid;
	while @@FETCH_STATUS=0
	begin
	
	set @message='Project #'+@sprojectid+ ' became active over 60 days ago on '+convert(varchar,@dudate,1)+
	' and there has been no Payment Request activity against it';
	
	insert into actionablealertsmessage(actionablealertsid,message,date,url,programofficer,regionaldirector,programassistant,regionid) 
	values(10,@message,@dudate+60,'showCipeprojectSummary.do?id='+cast(@projectid as varchar(20)),@uid1,@uid2,@uid3,@regionid);
	fetch next from AA10_cur into @projectid,@dudate,@sprojectid,@uid1,@uid2,@uid3,@regionid; 
	END;
	close AA10_cur;
	deallocate AA10_cur;
	--Partner Grant Signature Not Received	Partner Grant - View	
	--type' = "Partner Grant", 'Start Date' > Today() and 'status' = "Pending" or "Active"and 
	--and 'Receipt of Signed Agreement' is null.	
	--The start date of Grant # <project id> is set for <start date> for Partner <Partner Name> but the Signed Signature agreement has not been received
	DECLARE AA11_cur  cursor for SELECT project.id, project.projectstartDate,project.projectid,project.programofficer,project.regionaldirector,
	project.programassistant,project.regionid 
	FROM (project INNER JOIN projectstatus ON project.projectstatusid = projectstatus.id) INNER JOIN projecttype ON project.projecttypeid = projecttype.id
	WHERE (((projecttype.projecttype)='partner') AND ((project.projectstartDate)<getDate()) AND ((projectstatus.statusname)='pending' Or (projectstatus.statusname)='active') AND ((project.receiptofsignedagreement) Is Null));
	open AA11_cur
	fetch next from AA11_cur into @projectid,@dudate,@sprojectid,@uid1,@uid2,@uid3,@regionid;
	while @@FETCH_STATUS=0
	begin
	select @partnername= partnername from partner where id=(select partnerid from project where id=@projectid);
	
	set @message='The start date of Grant #'+@sprojectid+ ' is set for '+convert(varchar,@dudate,1) +
	'for Partner '+@partnername+ ' but the Signed Signature agreement has not been received';
	IF @message is not null 
	insert into actionablealertsmessage(actionablealertsid,message,date,url,programofficer,regionaldirector,programassistant,regionid) 
	values(11,@message,@dudate+1,'showEditPartnergrantView.do?id='+cast(@projectid as varchar(20)),@uid1,@uid2,@uid3,@regionid);
	fetch next from AA11_cur into @projectid,@dudate,@sprojectid,@uid1,@uid2,@uid3,@regionid; 
	END;
	close AA11_cur;
	deallocate AA11_cur;
	
	-- Donor Award Ending	Donor Awards – View should move to Action alert section	 
	-- 'End Date' Between Date() And Date()+90 and status = "Active"
	--Donor Award # <Award_Id> is scheduled to expire in less than 90 days, on <end_date>.
	
	DECLARE AA12_cur  cursor for select donoraward.donorawardnumber,donoraward.id,donoraward.enddate from (donoraward LEFT JOIN donor ON donoraward.donorawardtypeid = donor.id) LEFT JOIN donorawardstatus ON donoraward.donorawardstatusid = donorawardstatus.id
		WHERE (((donoraward.enddate) Between getDate() And getDate()+90) AND ((donorawardstatus.statusname)='active'));
	open AA12_cur
	fetch next from AA12_cur into @sprojectid,@id,@dudate;
	while @@FETCH_STATUS=0
	begin
	set @message='Donor Award # '+@sprojectid+' is scheduled to expire in less than 90 days, on '+convert(varchar,@dudate,1);
	insert into actionablealertsmessage(actionablealertsid,message,date,url) values(12,@message,@dudate-90,'showEditDonoraward.do?id='+cast(@id as varchar(20)));
	fetch next from AA12_cur into @sprojectid,@id,@dudate;
	END;
	close AA12_cur;
	deallocate AA12_cur;
	execute sp_rename 'Cacheactionablealertsmessage' ,'oldCacheactionablealertsmessage';
	execute sp_rename 'actionablealertsmessage' ,'Cacheactionablealertsmessage';
	execute sp_rename 'oldCacheactionablealertsmessage' ,'actionablealertsmessage';
	truncate table actionablealertsmessage;
	
END;

GO

-- ----------------------------
-- Function structure for [ActiveProjectListForReport]
-- ----------------------------


-- ----------------------------
-- Function structure for [ActiveProjectListForReport]
-- ----------------------------
CREATE FUNCTION [ActiveProjectListForReport](@reagin varchar(255),@status  varchar(200),@donorName varchar(200),@donorawardnumber varchar(255),
@ProgramOfficer varchar(255))
RETURNS TABLE 
RETURN 
SELECT project.projectid AS [Cipe Grant #], project.projecttitle AS [Project Title], 
project.acronym, region.regionname AS [Region Name], country.countryname AS [Country Name], 
project.projectstartdate AS [Project Start Date], project.projectenddate AS [Project End Date], 
projectstatus.statusname AS [Project Status], donor.donorname AS [Donor Name], 
donoraward.donorawardnumber AS [Donor Award #], 
userinfo.[firstname]+[lastname] AS [Program Officer], projectstatus.statusname
FROM (((((project LEFT JOIN region ON project.regionid = region.id) LEFT JOIN country 
ON project.countryid = country.id) LEFT JOIN projectstatus 
ON project.projectstatusid = projectstatus.id) LEFT JOIN donoraward 
ON project.donorawardid = donoraward.id) LEFT JOIN donor 
ON donoraward.donorid = donor.id) LEFT JOIN userinfo 
ON project.programofficer = userinfo.id
WHERE (((region.regionname)=@reagin Or @reagin = ' ') 
AND ((projectstatus.statusname)=@status Or @status = ' ') 
AND ((donor.donorname)= @donorName Or @donorName = ' ') 
AND ((donoraward.donorawardnumber)=@donorawardnumber Or @donorawardnumber = ' ') 
AND ((userinfo.[firstname] +[lastname])=@ProgramOfficer Or @ProgramOfficer = ' ') 
);

GO

-- ----------------------------
-- Function structure for [Auditor_21_List]
-- ----------------------------


---DB21 - Auditor Analysis & Selection Sheets (AASS) due within 30 days	Partner Grant – Find	
--FA Selection Memo Due Date' < Today()+30 and 'FA Selection Memo Received' Is Null ---
CREATE FUNCTION [Auditor_21_List]
    (  ) 
RETURNS TABLE 
RETURN
select * from project where projecttypeid=(select ID from [projecttype] where [projecttype]='Partner') and id in
(select projectid from audits WHERE (((audits.faselectionmemoduedate) Between getDate() And getDate()+30) AND ((audits.faselectionmemoreceived) Is Null)))


GO

-- ----------------------------
-- Function structure for [Auditor_22_List]
-- ----------------------------


---DB22 - Auditor Analysis & Selection Sheets (AASS) Past Due	Partner Grant – Find	
--FA Selection Memo Due Date' < Today() and 'FA Selection Memo Received' Is Null ---
CREATE FUNCTION [Auditor_22_List]
    (  ) 
RETURNS TABLE 
RETURN
select * from project where projecttypeid=(select ID from [projecttype] where [projecttype]='Partner') and id in
(select projectid from audits where faselectionmemoduedate<GETDATE() and faselectionmemoreceived is null)


GO

-- ----------------------------
-- Function structure for [Auditor_24_List]
-- ----------------------------


--forget insert id 23 in dashboard link--
--- DB23 - Interim Audits Due within 30 days	
--Partner Grant – Find	IA Due Date' <= Today()+30 and 'IA Received' Is Null---
CREATE FUNCTION [Auditor_24_List]
    (  ) 
RETURNS TABLE 
RETURN
select * from project where projecttypeid=(select ID from [projecttype] where [projecttype]='Partner') and id in
(select projectid from audits where audits.iadue Between getDate() And getDate()+30 and iareceived is null)


GO

-- ----------------------------
-- Function structure for [Auditor_25_List]
-- ----------------------------


---	DB24 - Final Audits Due within 30 days	Partner Grant – Find	
---Date Final Audit Due' <= Today()+30 and 'Date Final Audit Received' Is Null.---
CREATE FUNCTION [Auditor_25_List]
    (  ) 
RETURNS TABLE 
RETURN
SELECT project.*
FROM (audits INNER JOIN project ON audits.projectid = project.id) INNER JOIN projecttype ON project.projecttypeid = projecttype.id
WHERE (((audits.fadue) Between getDate() And getDate()+30) AND ((audits.fareceived) Is Null) AND ((projecttype.projecttype)='partner'));



GO

-- ----------------------------
-- Function structure for [Auditor_26_List]
-- ----------------------------


---DB25 - Interim Audit Date Past Due	Partner Grant – Find	
--IA Due Date' < Today() and 'IA Received' Is Null---
CREATE FUNCTION [Auditor_26_List]
    (  ) 
RETURNS TABLE 
RETURN
 SELECT project.*
	FROM (audits INNER JOIN project ON audits.projectid = project.id) INNER JOIN projecttype ON project.projecttypeid = projecttype.id
	WHERE (((audits.iadue)<getDate()) AND ((audits.iareceived) Is Null) AND ((projecttype.projecttype)='partner'))



GO

-- ----------------------------
-- Function structure for [Auditor_27_List]
-- ----------------------------


--- DB26 - Final Audit Date Past Due	Partner Grant – Find	
--Date Final Audit Due' < Today() and 'Date Final Audit Received' Is Null.	---

CREATE FUNCTION [Auditor_27_List]
    (  ) 
RETURNS TABLE 
RETURN
SELECT project.*
FROM (audits INNER JOIN project ON audits.projectid = project.id) INNER JOIN projecttype ON project.projecttypeid = projecttype.id
WHERE (((audits.fadue)<getDate()) AND ((audits.fareceived) Is Null) AND ((projecttype.projecttype)='partner'))



GO

-- ----------------------------
-- Function structure for [Donor_28_List]
-- ----------------------------


--- End of Reports---
---Donor Awards---
CREATE FUNCTION [Donor_28_List]
    (  ) 
RETURNS TABLE 
RETURN
SELECT donoraward.*
FROM donoraward LEFT JOIN donorawardstatus ON donoraward.donorawardstatusid = donorawardstatus.id
WHERE (((donoraward.startdate)>=getDate()-30 And (donoraward.startdate)<=getDate()) AND ((donorawardstatus.statusname)='active'));



GO

-- ----------------------------
-- Function structure for [Donor_29_List]
-- ----------------------------



CREATE FUNCTION [Donor_29_List]
    (  ) 
RETURNS TABLE 
RETURN
SELECT donoraward.*
FROM donoraward LEFT JOIN donorawardstatus ON donoraward.donorawardstatusid = donorawardstatus.id
WHERE (((donoraward.enddate) Between getDate() And getDate()+90) AND ((donorawardstatus.statusname)='active'));


GO

-- ----------------------------
-- Function structure for [get_dashboard_filter_count]
-- ----------------------------


CREATE FUNCTION [get_dashboard_filter_count](@id int, @reginid int) RETURNS @tempCount TABLE
(
 
 id int identity(1,1) primary key,
 linkName varchar(300),
 url varchar(255),
 count int
 ) 
AS 

BEGIN
DECLARE @count int;
DECLARE @url varchar(255);
DECLARE @statusidActive int;
	DECLARE @statusidPending int ;
	DECLARE @projectTypeIdPartener int;
	DECLARE @statusidOver int ;
	
	
	Declare @paymentrequestamount numeric(15,2);
	 
	SELECT @statusidActive=ID from projectstatus where statusname='Active';
	SELECT @statusidPending=ID from projectstatus where statusname='Pending';
	SELECT @statusidOver=ID from projectstatus where statusname='Over';
	
	SELECT @projectTypeIdPartener=ID from [projecttype] where [projecttype]='Partner';
	
	select @count=count(*) from project where projectstatusid=@statusidActive and projecttypeid!=@projectTypeIdPartener
	and (project.programofficer=ISNULL(@id,project.programofficer) or project.regionaldirector=ISNULL(@id,project.programofficer) or project.programassistant=ISNULL(@id,project.programofficer))
	and regionid=ISNULL(@reginid,regionid);
	set @url='findResultCipeprojectWithProcedure.do?prc=Project_DB1('+isnull(CAST(@id as varchar(100)),'null')+','+isnull(CAST(@reginid as varchar(100)),'null')+')&message=Project+Status+=+&quot;Active&quot;';	
	insert into @tempCount(url,count,linkName) values (@url,@count,'Project Status = "Active"');
	
	select @count=count(*) from project where projectstatusid=@statusidOver and projecttypeid!=@projectTypeIdPartener
	and (project.programofficer=ISNULL(@id,project.programofficer) or project.regionaldirector=ISNULL(@id,project.programofficer) or project.programassistant=ISNULL(@id,project.programofficer))
	and regionid=ISNULL(@reginid,regionid);
	
	set @url='findResultCipeprojectWithProcedure.do?prc=Project_DB2('+isnull(CAST(@id as varchar(100)),'null')+','+isnull(CAST(@reginid as varchar(100)),'null')+')&message=Project+Status+=+&quot;Over&quot;';	
	insert into @tempCount(url,count,linkName) values (@url,@count,'Project Status = "Over"');
	
	SELECT @count=Count(project.id) 
	FROM (project INNER JOIN projecttype ON project.projecttypeid = projecttype.id) INNER JOIN projectstatus ON project.projectstatusid = projectstatus.id
	WHERE ((Not (projecttype.projecttype)='partner') AND ((projectstatus.statusname)='active') AND ((project.projectenddate)>=getDate() And (project.projectenddate)<=getDate()+30))
	and (project.programofficer=ISNULL(@id,project.programofficer) or project.regionaldirector=ISNULL(@id,project.programofficer) or project.programassistant=ISNULL(@id,project.programofficer))
	and regionid=ISNULL(@reginid,regionid);
	
	set @url='findResultCipeprojectWithProcedure.do?prc=Project_DB3('+isnull(CAST(@id as varchar(100)),'null')+','+isnull(CAST(@reginid as varchar(100)),'null')+')&message=Projects+&quot;Over&quot;+within+30+days';
	insert into @tempCount(url,count,linkName) values (@url,@count,'Projects "Over" within 30 days');	
	
	select @count=count(*) from project where projectstatusid=@statusidActive and projecttypeid!=@projectTypeIdPartener 
	and DATEDIFF(DAY,GETDATE(),projectstartdate)<=60 and
	0=(select count(*) from paymentrequestlineitem where projectid=project.id )
	and (project.programofficer=ISNULL(@id,project.programofficer) or project.regionaldirector=ISNULL(@id,project.programofficer) or project.programassistant=ISNULL(@id,project.programofficer))
	and regionid=ISNULL(@reginid,regionid);
	
	set @url='findResultCipeprojectWithProcedure.do?prc=Project_DB4('+isnull(CAST(@id as varchar(100)),'null')+','+isnull(CAST(@reginid as varchar(100)),'null')+')&message=New+Projects+where+no+Payment+Activity+>=+60+days';
	
	insert into @tempCount(url,count,linkName) values (@url,@count,'New Projects where no Payment Activity &gt;= 60 days');	
	
	
	select @count=count(*) from project where projectstatusid=@statusidActive and projecttypeid!=@projectTypeIdPartener 
	and project.managementfunds!=0 and
	(select SUM(paymentrequestamount) from paymentrequestlineitem 
	where projectid=project.id)/project.managementfunds>=0.9
	and (project.programofficer=ISNULL(@id,project.programofficer) or project.regionaldirector=ISNULL(@id,project.programofficer) or project.programassistant=ISNULL(@id,project.programofficer))
	and regionid=ISNULL(@reginid,regionid);
	
	set @url='findResultCipeprojectWithProcedure.do?prc=Project_DB5('+isnull(CAST(@id as varchar(100)),'null')+','+isnull(CAST(@reginid as varchar(100)),'null')+')&message=&quot;Active&quot;+Projects+with+more+than+90%25+Budget+Disbursed';
	insert into @tempCount(url,count,linkName) values (@url,@count,'"Active" Projects with more than 90% Budget Disbursed ');	
	
	select @count=count(*) from project 
	where projectstatusid=@statusidActive and projecttypeid=@projectTypeIdPartener
	and (project.programofficer=ISNULL(@id,project.programofficer) or project.regionaldirector=ISNULL(@id,project.programofficer) or project.programassistant=ISNULL(@id,project.programofficer))
	and regionid=ISNULL(@reginid,regionid);
	
	set @url='findResultPartnergrantWithProcedure.do?prc=Project_DB6('+isnull(CAST(@id as varchar(100)),'null')+','+isnull(CAST(@reginid as varchar(100)),'null')+')&message=Partner+Grant+Status+=+&quot;Active&quot;';
	insert into @tempCount(url,count,linkName) values (@url,@count,'Partner Grant Status = "Active"');	
	
	select @count=count(*) from project 
	where projectstatusid=@statusidOver and projecttypeid=@projectTypeIdPartener
	and (project.programofficer=ISNULL(@id,project.programofficer) or project.regionaldirector=ISNULL(@id,project.programofficer) or project.programassistant=ISNULL(@id,project.programofficer))
	and regionid=ISNULL(@reginid,regionid);
	
	set @url='findResultPartnergrantWithProcedure.do?prc=Project_DB7('+isnull(CAST(@id as varchar(100)),'null')+','+isnull(CAST(@reginid as varchar(100)),'null')+')&message=Partner+Grant+Status+=+&quot;Over&quot;';
	insert into @tempCount(url,count,linkName) values (@url,@count,'Partner Grant Status = "Over"');
	
	SELECT @count=Count(project.id) 
	FROM (project INNER JOIN projecttype ON project.projecttypeid = projecttype.id) INNER JOIN projectstatus ON project.projectstatusid = projectstatus.id
	WHERE (((projecttype.projecttype)='partner') AND ((projectstatus.statusname)='active') AND ((project.projectstartdate)<getDate()) AND ((project.receiptofsignedagreement) Is Null))	
	and (project.programofficer=ISNULL(@id,project.programofficer) or project.regionaldirector=ISNULL(@id,project.programofficer) or project.programassistant=ISNULL(@id,project.programofficer))
	and regionid=ISNULL(@reginid,regionid);
	
	set @url='findResultPartnergrantWithProcedure.do?prc=Project_DB8('+isnull(CAST(@id as varchar(100)),'null')+','+isnull(CAST(@reginid as varchar(100)),'null')+')&message=New+Partner+Grants+where+Signed+Grant+Agreement+Not+Received';
	insert into @tempCount(url,count,linkName) values (@url,@count,'New Partner Grants where Signed Grant Agreement Not Received');
	
	select @count=count(*) from project where projectstatusid=@statusidActive and projecttypeid=@projectTypeIdPartener and
	DATEDIFF(DAY,GETDATE(),projectstartdate)<=60 and 
	0=(select count(*) from paymentrequestlineitem where projectid=project.id )
	and (project.programofficer=ISNULL(@id,project.programofficer) or project.regionaldirector=ISNULL(@id,project.programofficer) or project.programassistant=ISNULL(@id,project.programofficer))
	and regionid=ISNULL(@reginid,regionid);
	
	set @url='findResultPartnergrantWithProcedure.do?prc=Project_DB9('+isnull(CAST(@id as varchar(100)),'null')+','+isnull(CAST(@reginid as varchar(100)),'null')+')&message=New+Partner+Grants+with+no+Payment+Activity+greater+>=+60+days';
	insert into @tempCount(url,count,linkName) values (@url,@count,'New Partner Grants with no Payment Activity greater &gt;= 60 days');
	
	select @count=count(*) from project where projectstatusid=@statusidActive and projecttypeid=@projectTypeIdPartener 
	and project.managementfunds!=0
	and
   (select SUM(paymentrequestamount) from paymentrequestlineitem where projectid=project.id)/project.managementfunds>=0.9
   and (project.programofficer=ISNULL(@id,project.programofficer) or project.regionaldirector=ISNULL(@id,project.programofficer) or project.programassistant=ISNULL(@id,project.programofficer))
	and regionid=ISNULL(@reginid,regionid);
	
	set @url='findResultPartnergrantWithProcedure.do?prc=Project_DB10('+isnull(CAST(@id as varchar(100)),'null')+','+isnull(CAST(@reginid as varchar(100)),'null')+')&message=&quot;Active&quot;+Partner+Grants+with+more+than+90%25+Budget+Disbursed';
	insert into @tempCount(url,count,linkName) values (@url,@count,'"Active" Partner Grants with more than 90% Budget Disbursed');
	
		
RETURN;

end


GO

-- ----------------------------
-- Function structure for [get_donorAward_count]
-- ----------------------------



CREATE FUNCTION [get_donorAward_count]() RETURNS @tempCount TABLE
(
 id int identity(28,1) primary key,
 linkName varchar(300),
 url varchar(255),
 count int
 ) 
AS 

BEGIN
DECLARE @count int;
DECLARE @url varchar(255);

SELECT @count=count(*)
FROM donoraward LEFT JOIN donorawardstatus ON donoraward.donorawardstatusid = donorawardstatus.id
WHERE (((donoraward.startdate)>=getDate()-30 And (donoraward.startdate)<=getDate()) AND ((donorawardstatus.statusname)='active'))
set @url='findResultDonorawardWithProcedure.do?prc=Donor_28_List()&message=New+Donor+Awards+(less+than+30+days+old)';
insert into @tempCount(url,count,linkName) values (@url,@count,'New Donor Awards (less than 30 days old)');	

SELECT @count=count(*)
FROM donoraward LEFT JOIN donorawardstatus ON donoraward.donorawardstatusid = donorawardstatus.id
WHERE (((donoraward.enddate) Between getDate() And getDate()+90) AND ((donorawardstatus.statusname)='active'))

set @url='findResultDonorawardWithProcedure.do?prc=Donor_29_List()&message=Donor+Awards+&quot;Over&quot;+in+less+than+90+days';

insert into @tempCount(url,count,linkName) values (@url,@count,'Donor Awards &quot;Over&quot; in less than 90 days');	
		
RETURN;
end

GO

-- ----------------------------
-- Function structure for [get_payment_request_count]
-- ----------------------------



CREATE FUNCTION [get_payment_request_count](@id int) RETURNS @tempCount TABLE
(
 id int identity(11,1) primary key,
 linkName varchar(300),
 url varchar(255),
 count int
 ) 
AS 

BEGIN
DECLARE @count int;
DECLARE @url varchar(255);
	-- Payment Request--
	--- - # of Open Payment Requests	Payment Request – 
	--Find	Paymentrequest.status <> “Cancelled” or “Paid”---
select @count=count(*) from paymentrequest where statuscode!='Cancelled' and statuscode!='Paid' 
and paymentrequest.createdby= isnull(@id,createdby)
set @url='findResultPaymentrequestWithprocedure.do?prc=Payment_DB11('+isnull(CAST(@id as varchar(100)),'null')+')&message=%23+of+Open+Payment+Requests';
insert into @tempCount(url,count,linkName) values (@url,@count,'# of Open Payment Requests');	

select @count=count(*) from paymentrequest where statuscode!='Cancelled' and statuscode!='Paid' 
and datediff(day,GETDATE(),createdate)<=-30
and paymentrequest.createdby= isnull(@id,createdby)
set @url='findResultPaymentrequestWithprocedure.do?prc=Payment_DB12('+isnull(CAST(@id as varchar(100)),'null')+')&message=Payment+Requests+that+have+been+open+for+greater+than+30+days';
insert into @tempCount(url,count,linkName) values (@url,@count,'Payment Requests that have been open for greater than 30 days');	

SELECT @count=count(*)
	FROM paymentrequest LEFT JOIN paymenttype ON paymentrequest.paymenttypeid = paymenttype.id
	WHERE (((paymentrequest.statuscode)='paid') AND ((paymentrequest.datepaid) Between getDate()-30 And getDate()))
	and paymentrequest.createdby= isnull(@id,paymentrequest.createdby)
	
set @url='findResultPaymentrequestWithprocedure.do?prc=Payment_DB13('+isnull(CAST(@id as varchar(100)),'null')+')&message=Paid+Payment+Requests,+past+30+days';
insert into @tempCount(url,count,linkName) values (@url,@count,'Paid Payment Requests, past 30 days');	

SELECT @count=Count(paymentrequest.id) 
	FROM paymentrequest LEFT JOIN paymenttype ON paymentrequest.paymenttypeid = paymenttype.id
	WHERE (((paymentrequest.statuscode)='rejected'))
	and paymentrequest.createdby= isnull(@id,paymentrequest.createdby)
	
set @url='findResultPaymentrequestWithprocedure.do?prc=Payment_DB14('+isnull(CAST(@id as varchar(100)),'null')+')&message=%23+Rejected+Payment+Requests';
insert into @tempCount(url,count,linkName) values (@url,@count,'# Rejected Payment Requests');	
	
RETURN;
end

GO

-- ----------------------------
-- Function structure for [get_PM_filter_count]
-- ----------------------------


CREATE FUNCTION [get_PM_filter_count](@id int, @reginid int) RETURNS @tempCount TABLE
(
 id int identity(15,1) primary key,
 linkName varchar(300),
 url varchar(255),
 count int
 ) 
AS 

BEGIN
DECLARE @count int;
DECLARE @url varchar(255);
DECLARE @statusidActive int;
	DECLARE @statusidPending int ;
	DECLARE @projectTypeIdPartener int;
	DECLARE @statusidOver int ;
	
	
	Declare @paymentrequestamount numeric(15,2);
	 
	SELECT @statusidActive=ID from projectstatus where statusname='Active';
	SELECT @statusidPending=ID from projectstatus where statusname='Pending';
	SELECT @statusidOver=ID from projectstatus where statusname='Over';
	
	SELECT @projectTypeIdPartener=ID from [projecttype] where [projecttype]='Partner';
	
	select @count=count(*) from project where projecttypeid=(select ID from [projecttype] where [projecttype]='Partner') and id in
	(select projectid from projectreports pr where type='Narrative' and reportreceived is null and  datediff(day,GETDATE(),reportdue)<=30 )
	and (project.programofficer=ISNULL(@id,project.programofficer) or project.regionaldirector=ISNULL(@id,project.programofficer) or project.programassistant=ISNULL(@id,project.programofficer))
	and regionid=ISNULL(@reginid,regionid);
	
	set @url='findResultPartnergrantWithProcedure.do?prc=PM_DB15('+isnull(CAST(@id as varchar(100)),'null')+','+isnull(CAST(@reginid as varchar(100)),'null')+')&message=Narrative+Reports+due+within+30+days';	
	insert into @tempCount(url,count,linkName) values (@url,@count,'Narrative Reports due within 30 days');
	
	select @count=count(*)  from project where projecttypeid=(select ID from [projecttype] where [projecttype]='Partner') and id in 
(select projectid from projectreports pr where type='Financial' and reportreceived is null and  datediff(day,GETDATE(),reportdue)<=30)
 and (project.programofficer=ISNULL(@id,project.programofficer) or project.regionaldirector=ISNULL(@id,project.programofficer) or project.programassistant=ISNULL(@id,project.programofficer))
and regionid=ISNULL(@reginid,regionid);
	
	set @url='findResultPartnergrantWithProcedure.do?prc=PM_DB16('+isnull(CAST(@id as varchar(100)),'null')+','+isnull(CAST(@reginid as varchar(100)),'null')+')&message=Financial+Reports+due+within+30+days';	
	insert into @tempCount(url,count,linkName) values (@url,@count,'Financial Reports due within 30 days');
	
	select  @count=count(*) from project where projecttypeid=(select ID from [projecttype] where [projecttype]='Partner') and id in
(select projectid from projectreports pr where type='Narrative' and reportreceived is null and  datediff(day,GETDATE(),reportdue)<0 )
 and (project.programofficer=ISNULL(@id,project.programofficer) or project.regionaldirector=ISNULL(@id,project.programofficer) or project.programassistant=ISNULL(@id,project.programofficer))
and regionid=ISNULL(@reginid,regionid);

	
	set @url='findResultPartnergrantWithProcedure.do?prc=PM_DB17('+isnull(CAST(@id as varchar(100)),'null')+','+isnull(CAST(@reginid as varchar(100)),'null')+')&message=Narrative+Reports+Past+Due';	
	insert into @tempCount(url,count,linkName) values (@url,@count,'Narrative Reports Past Due');
	
	---DB18----
	
	select @count=count(*) from project where projecttypeid=(select ID from [projecttype] where [projecttype]='Partner') and id 
in(select projectid from projectreports pr where type='Financial' and reportreceived is null and  datediff(day,GETDATE(),reportdue)<0)
and (project.programofficer=ISNULL(@id,project.programofficer) or project.regionaldirector=ISNULL(@id,project.programofficer) or project.programassistant=ISNULL(@id,project.programofficer))
and regionid=ISNULL(@reginid,regionid);
	
	set @url='findResultPartnergrantWithProcedure.do?prc=PM_DB18('+isnull(CAST(@id as varchar(100)),'null')+','+isnull(CAST(@reginid as varchar(100)),'null')+')&message=Financial+Reports+Past+Due';	
	insert into @tempCount(url,count,linkName) values (@url,@count,'Financial Reports Past Due');
	
	--DB19--
	select @count=count(*) from project where projecttypeid=(select ID from [projecttype] where [projecttype]='Partner') and id in(
select projectid FROM imp
WHERE (((imp.impduedate) Between getDate() And getDate()+30) AND ((imp.impperformed) Is Null)) )
and (project.programofficer=ISNULL(@id,project.programofficer) or project.regionaldirector=ISNULL(@id,project.programofficer) or project.programassistant=ISNULL(@id,project.programofficer))
and regionid=ISNULL(@reginid,regionid);
	
	set @url='findResultPartnergrantWithProcedure.do?prc=PM_DB19('+isnull(CAST(@id as varchar(100)),'null')+','+isnull(CAST(@reginid as varchar(100)),'null')+')&message=IMP''s+due+within+30+days';	
	insert into @tempCount(url,count,linkName) values (@url,@count,'IMPs due within 30 days');
	
	--DB20--
	select @count=count(*) from project where projecttypeid=(select ID from [projecttype] where [projecttype]='Partner') and id in(
select projectid from imp where  datediff(day,GETDATE(),impduedate)<0  and impperformed is null)
and (project.programofficer=ISNULL(@id,project.programofficer) or project.regionaldirector=ISNULL(@id,project.programofficer) or project.programassistant=ISNULL(@id,project.programofficer))
and regionid=ISNULL(@reginid,regionid);
	
	set @url='findResultPartnergrantWithProcedure.do?prc=PM_DB20('+isnull(CAST(@id as varchar(100)),'null')+','+isnull(CAST(@reginid as varchar(100)),'null')+')&message=IMP''s+Past+Due';	
	insert into @tempCount(url,count,linkName) values (@url,@count,'IMPs Past Due');
	
	--DB21--
	
	select @count=count(*) from project where projecttypeid=(select ID from [projecttype] where [projecttype]='Partner') and id in
(select projectid from audits WHERE (((audits.faselectionmemoduedate) Between getDate() And getDate()+30) AND ((audits.faselectionmemoreceived) Is Null)))
and (project.programofficer=ISNULL(@id,project.programofficer) or project.regionaldirector=ISNULL(@id,project.programofficer) or project.programassistant=ISNULL(@id,project.programofficer))
and regionid=ISNULL(@reginid,regionid);
	
	set @url='findResultPartnergrantWithProcedure.do?prc=PM_DB21('+isnull(CAST(@id as varchar(100)),'null')+','+isnull(CAST(@reginid as varchar(100)),'null')+')&message=Auditor+Analysis+%26+Selection+Sheets+(AASS)+due+within+30+days';	
	insert into @tempCount(url,count,linkName) values (@url,@count,'Auditor Analysis &amp; Selection Sheets (AASS) due within 30 days');
	--DB22--
	
	select @count=count(*) from project where projecttypeid=(select ID from [projecttype] where [projecttype]='Partner') and id in
(select projectid from audits where faselectionmemoduedate<GETDATE() and faselectionmemoreceived is null)
and (project.programofficer=ISNULL(@id,project.programofficer) or project.regionaldirector=ISNULL(@id,project.programofficer) or project.programassistant=ISNULL(@id,project.programofficer))
and regionid=ISNULL(@reginid,regionid);
	
	set @url='findResultPartnergrantWithProcedure.do?prc=PM_DB22('+isnull(CAST(@id as varchar(100)),'null')+','+isnull(CAST(@reginid as varchar(100)),'null')+')&message=Auditor+Analysis+%26+Selection+Sheets+(AASS)+Past+Due';	
	insert into @tempCount(url,count,linkName) values (@url,@count,'Auditor Analysis &amp; Selection Sheets (AASS) Past Due');
	
	--DB23--
	
	select @count=count(*) from project where projecttypeid=(select ID from [projecttype] where [projecttype]='Partner') and id in
	(select projectid from audits where audits.iadue Between getDate() And getDate()+30 and iareceived is null)
	and (project.programofficer=ISNULL(@id,project.programofficer) or project.regionaldirector=ISNULL(@id,project.programofficer) or project.programassistant=ISNULL(@id,project.programofficer))
	and regionid=ISNULL(@reginid,regionid);
	
	set @url='findResultPartnergrantWithProcedure.do?prc=PM_DB24('+isnull(CAST(@id as varchar(100)),'null')+','+isnull(CAST(@reginid as varchar(100)),'null')+')&message=Interim+Audits+Due+within+30+days';	
	insert into @tempCount(url,count,linkName) values (@url,@count,'Interim Audits Due within 30 days ');
	
	--DB24--
	
	SELECT @count=count(project.id)
FROM (audits INNER JOIN project ON audits.projectid = project.id) INNER JOIN projecttype ON project.projecttypeid = projecttype.id
WHERE (((audits.fadue) Between getDate() And getDate()+30) AND ((audits.fareceived) Is Null) AND ((projecttype.projecttype)='partner'))
and (project.programofficer=ISNULL(@id,project.programofficer) or project.regionaldirector=ISNULL(@id,project.programofficer) or project.programassistant=ISNULL(@id,project.programofficer))
and regionid=ISNULL(@reginid,regionid);

	set @url='findResultPartnergrantWithProcedure.do?prc=PM_DB25('+isnull(CAST(@id as varchar(100)),'null')+','+isnull(CAST(@reginid as varchar(100)),'null')+')&message=Final+Audits+Due+within+30+days';	
	insert into @tempCount(url,count,linkName) values (@url,@count,'Final Audits Due within 30 days');
	---DB25---
	
	SELECT @count=count(project.id)
	FROM (audits INNER JOIN project ON audits.projectid = project.id) INNER JOIN projecttype ON project.projecttypeid = projecttype.id
	WHERE (((audits.iadue)<getDate()) AND ((audits.iareceived) Is Null) AND ((projecttype.projecttype)='partner'))
	and (project.programofficer=ISNULL(@id,project.programofficer) or project.regionaldirector=ISNULL(@id,project.programofficer) or project.programassistant=ISNULL(@id,project.programofficer))
and regionid=ISNULL(@reginid,regionid);
	
	set @url='findResultPartnergrantWithProcedure.do?prc=PM_DB26('+isnull(CAST(@id as varchar(100)),'null')+','+isnull(CAST(@reginid as varchar(100)),'null')+')&message=Interim+Audit+Date+Past+Due';	
	insert into @tempCount(url,count,linkName) values (@url,@count,'Interim Audit Date Past Due');
	--DB26--
	SELECT @count=count(project.id)
	FROM (audits INNER JOIN project ON audits.projectid = project.id) INNER JOIN projecttype ON project.projecttypeid = projecttype.id
	WHERE (((audits.fadue)<getDate()) AND ((audits.fareceived) Is Null) AND ((projecttype.projecttype)='partner'))
	and (project.programofficer=ISNULL(@id,project.programofficer) or project.regionaldirector=ISNULL(@id,project.programofficer) or project.programassistant=ISNULL(@id,project.programofficer))
and regionid=ISNULL(@reginid,regionid);

	
	set @url='findResultPartnergrantWithProcedure.do?prc=PM_DB27('+isnull(CAST(@id as varchar(100)),'null')+','+isnull(CAST(@reginid as varchar(100)),'null')+')&message=Final+Audit+Date+Past+Due';	
	insert into @tempCount(url,count,linkName) values (@url,@count,'Final Audit Date Past Due');
	
		
		
RETURN;

end



GO

-- ----------------------------
-- Function structure for [getProjectGrantList]
-- ----------------------------


-- ----------------------------
-- Function structure for [getProjectGrantList]
-- ----------------------------



CREATE FUNCTION [getProjectGrantList]( @id int)
RETURNS TABLE 
RETURN 
select * from project where  projecttypeid =(select ID from [projecttype] where [projecttype]='Partner') and 
id in(SELECT projectid from paymentrequestlineitem where paymentrequestid=36);




GO

-- ----------------------------
-- Function structure for [getProjectList]
-- ----------------------------


-- ----------------------------
-- Function structure for [getProjectList]
-- ----------------------------



CREATE FUNCTION [getProjectList]( @id int)
RETURNS TABLE 
RETURN 
select * from project where  projecttypeid !=(select ID from [projecttype] where [projecttype]='Partner') and 
id in(SELECT projectid from paymentrequestlineitem where paymentrequestid=@id);



GO

-- ----------------------------
-- Function structure for [Imp_19_List]
-- ----------------------------


--- IMPs due within 30 days	Partner Grant – Find	
--IMP Due Date' <=Today()+30 and 'IMP Performed' Is Null---
CREATE FUNCTION [Imp_19_List]
    (  ) 
RETURNS TABLE 
RETURN
select * from project where projecttypeid=(select ID from [projecttype] where [projecttype]='Partner') and id in(
select projectid FROM imp
WHERE (((imp.impduedate) Between getDate() And getDate()+30) AND ((imp.impperformed) Is Null)) )


GO

-- ----------------------------
-- Function structure for [Imp_20_List]
-- ----------------------------


--- DB20 - IMP’s Past Due	Partner Grant – Find	IMP Due Date' <Today() and 'IMP Performed' Is Null.---
CREATE FUNCTION [Imp_20_List]
    (  ) 
RETURNS TABLE 
RETURN
select * from project where projecttypeid=(select ID from [projecttype] where [projecttype]='Partner') and id in(
select projectid from imp where  datediff(day,GETDATE(),impduedate)<0  and impperformed is null);


GO

-- ----------------------------
-- Function structure for [NarrativeReports_15_List]
-- ----------------------------


---Reports ---
--NarrativeReports due within 30 days --
--Partner Grant – Find	Type' = "Narrative" and 'Report Due' <= Today()+30 and 'Report Received' Is Null.--
CREATE FUNCTION [NarrativeReports_15_List]
    (  ) 
RETURNS TABLE 
RETURN 	
select * from project where projecttypeid=(select ID from [projecttype] where [projecttype]='Partner') and id in
(select projectid from projectreports pr where type='Narrative' and reportreceived is null and  datediff(day,GETDATE(),reportdue)<=30 );

GO

-- ----------------------------
-- Function structure for [NarrativeReports_16_List]
-- ----------------------------


--- Financial Reports due within 30 days	Partner Grant – Find	
--Type' = "Financial" and 'Report Due' <= Today()+30 and 'Report Received' Is Null.---
CREATE FUNCTION [NarrativeReports_16_List]
    (  ) 
RETURNS TABLE 
RETURN 	
select * from project where projecttypeid=(select ID from [projecttype] where [projecttype]='Partner') and id in 
(select projectid from projectreports pr where type='Financial' and reportreceived is null and  datediff(day,GETDATE(),reportdue)<=30);


GO

-- ----------------------------
-- Function structure for [NarrativeReports_17_List]
-- ----------------------------


--- Narrative Reports Past Due	Partner Grant – Find	
	--Type' = "Narrative" and 'Report Due' <Today() and 'Report Received' Is Null.---
CREATE FUNCTION [NarrativeReports_17_List]
    (  ) 
RETURNS TABLE 
RETURN 	
select * from project where projecttypeid=(select ID from [projecttype] where [projecttype]='Partner') and id in
(select projectid from projectreports pr where type='Narrative' and reportreceived is null and  datediff(day,GETDATE(),reportdue)<0 );
	

GO

-- ----------------------------
-- Function structure for [NarrativeReports_18_List]
-- ----------------------------


---Financial Reports Past Due	Partner Grant – Find	
--Type' = "Financial" and 'Report Due' <Today() and 'Report Received'  ---
CREATE FUNCTION [NarrativeReports_18_List]
    (  ) 
RETURNS TABLE 
RETURN 	
select * from project where projecttypeid=(select ID from [projecttype] where [projecttype]='Partner') and id 
in(select projectid from projectreports pr where type='Financial' and reportreceived is null and  datediff(day,GETDATE(),reportdue)<0)

GO

-- ----------------------------
-- Function structure for [Partner_Grant_Status_8_List]
-- ----------------------------


--Project.status = “Active” and Project.type = “Partner Grant” and project.start_date >= today() and project. ReceiptOfSignedAgreement IS NULL--
CREATE FUNCTION [Partner_Grant_Status_8_List]
    (  ) 
RETURNS TABLE 
RETURN 
SELECT project.*
FROM (project INNER JOIN projecttype ON project.projecttypeid = projecttype.id) INNER JOIN projectstatus ON project.projectstatusid = projectstatus.id
WHERE (((projecttype.projecttype)='partner') AND ((projectstatus.statusname)='active') AND ((project.projectstartdate)<getDate()) AND ((project.receiptofsignedagreement) Is Null));

GO

-- ----------------------------
-- Function structure for [Partner_Grant_Status_Active_List]
-- ----------------------------


---Project status active and project type partner grant ---
CREATE FUNCTION [Partner_Grant_Status_Active_List]
    (  ) 
RETURNS TABLE 
RETURN 
SELECT  * from project where  projectstatusid=(select ID from projectstatus where statusname='Active') 
and projecttypeid =(select ID from [projecttype] where [projecttype]='Partner') 


GO

-- ----------------------------
-- Function structure for [Partner_Grant_Status_Over_List]
-- ----------------------------


-- Partner Grant – Find	Project.status = “Over” and Project.type = “Partner Grant”---
CREATE FUNCTION [Partner_Grant_Status_Over_List]
    (  ) 
RETURNS TABLE 
RETURN 
SELECT  * from project where  projectstatusid=(select ID from projectstatus where statusname='Over') 
and projecttypeid =(select ID from [projecttype] where [projecttype]='Partner') 

GO

-- ----------------------------
-- Function structure for [Payment_11_List]
-- ----------------------------


	-- Payment Request--
	--- - # of Open Payment Requests	Payment Request – 
	--Find	Paymentrequest.status <> “Cancelled” or “Paid”---
CREATE FUNCTION [Payment_11_List]
    (  ) 
RETURNS TABLE 
RETURN 
SELECT * from paymentrequest where statuscode!='Cancelled' and statuscode!='Paid'	

GO

-- ----------------------------
-- Function structure for [Payment_12_List]
-- ----------------------------


--Payment Requests that have been open for greater than 30 days	Payment Request – 
	--Find	Paymentrequest.status <> “Cancelled” or “Paid” and paymentrequest.createdate <= Today() – 30--
CREATE FUNCTION [Payment_12_List]
    (  ) 
RETURNS TABLE 
RETURN 	
 select * from paymentrequest where statuscode!='Cancelled' and statuscode!='Paid' 
and datediff(day,GETDATE(),createdate)<=-30


GO

-- ----------------------------
-- Function structure for [Payment_13_List]
-- ----------------------------


--- Paid Payment Requests, past 30 days	Payment Request – 
--Find	Paymentrequest.status = “Paid” and paymentrequest.datepaid >= Today()– 30---
CREATE FUNCTION [Payment_13_List]
    (  ) 
RETURNS TABLE 
RETURN 
SELECT paymentrequest.*
FROM paymentrequest LEFT JOIN paymenttype ON paymentrequest.paymenttypeid = paymenttype.id
WHERE (((paymentrequest.statuscode)='paid') AND ((paymentrequest.datepaid) Between getDate()-30 And getDate()))


GO

-- ----------------------------
-- Function structure for [Payment_14_List]
-- ----------------------------


---Rejected Payment Requests Payment Request – Find	Paymentrequest.status <> “Rejected”---
CREATE FUNCTION [Payment_14_List]
    (  ) 
RETURNS TABLE 
RETURN 	
SELECT paymentrequest.* 
FROM paymentrequest LEFT JOIN paymenttype ON paymentrequest.paymenttypeid = paymenttype.id
WHERE (((paymentrequest.statuscode)='rejected'));


GO

-- ----------------------------
-- Function structure for [Payment_DB11]
-- ----------------------------


CREATE FUNCTION [Payment_DB11](@id  int)
RETURNS TABLE 
RETURN 
SELECT * from paymentrequest where statuscode!='Cancelled' and statuscode!='Paid'
and paymentrequest.createdby= isnull(@id,paymentrequest.createdby)

GO

-- ----------------------------
-- Function structure for [Payment_DB12]
-- ----------------------------


CREATE FUNCTION [Payment_DB12](@id  int)
RETURNS TABLE 
RETURN 
select * from paymentrequest where statuscode!='Cancelled' and statuscode!='Paid' 
and datediff(day,GETDATE(),createdate)<=-30
and paymentrequest.createdby= isnull(@id,paymentrequest.createdby)

GO

-- ----------------------------
-- Function structure for [Payment_DB13]
-- ----------------------------


CREATE FUNCTION [Payment_DB13](@id  int)
RETURNS TABLE 
RETURN 
SELECT paymentrequest.*
FROM paymentrequest LEFT JOIN paymenttype ON paymentrequest.paymenttypeid = paymenttype.id
WHERE (((paymentrequest.statuscode)='paid') AND ((paymentrequest.datepaid) Between getDate()-30 And getDate()))
and paymentrequest.createdby= isnull(@id,paymentrequest.createdby)


GO

-- ----------------------------
-- Function structure for [Payment_DB14]
-- ----------------------------


CREATE FUNCTION [Payment_DB14](@id  int)
RETURNS TABLE 
RETURN 
SELECT paymentrequest.* 
FROM paymentrequest LEFT JOIN paymenttype ON paymentrequest.paymenttypeid = paymenttype.id
WHERE (((paymentrequest.statuscode)='rejected'))
and paymentrequest.createdby= isnull(@id,paymentrequest.createdby)

GO

-- ----------------------------
-- Function structure for [PM_DB15]
-- ----------------------------


CREATE FUNCTION [PM_DB15](@id  int,@reginid int )
RETURNS TABLE 
RETURN 
select * from project where projecttypeid=(select ID from [projecttype] where [projecttype]='Partner') and id in
(select projectid from projectreports pr where type='Narrative' and reportreceived is null and  datediff(day,GETDATE(),reportdue)<=30 )
and (project.programofficer=ISNULL(@id,project.programofficer) or project.regionaldirector=ISNULL(@id,project.programofficer) or project.programassistant=ISNULL(@id,project.programofficer))
and regionid=ISNULL(@reginid,regionid);


GO

-- ----------------------------
-- Function structure for [PM_DB16]
-- ----------------------------


CREATE FUNCTION [PM_DB16](@id  int,@reginid int )
RETURNS TABLE 
RETURN 
select * from project where projecttypeid=(select ID from [projecttype] where [projecttype]='Partner') and id in 
(select projectid from projectreports pr where type='Financial' and reportreceived is null and  datediff(day,GETDATE(),reportdue)<=30)
 and (project.programofficer=ISNULL(@id,project.programofficer) or project.regionaldirector=ISNULL(@id,project.programofficer) or project.programassistant=ISNULL(@id,project.programofficer))
and regionid=ISNULL(@reginid,regionid);


GO

-- ----------------------------
-- Function structure for [PM_DB17]
-- ----------------------------


CREATE FUNCTION [PM_DB17](@id  int,@reginid int )
RETURNS TABLE 
RETURN 
select * from project where projecttypeid=(select ID from [projecttype] where [projecttype]='Partner') and id in
(select projectid from projectreports pr where type='Narrative' and reportreceived is null and  datediff(day,GETDATE(),reportdue)<0 )
 and (project.programofficer=ISNULL(@id,project.programofficer) or project.regionaldirector=ISNULL(@id,project.programofficer) or project.programassistant=ISNULL(@id,project.programofficer))
and regionid=ISNULL(@reginid,regionid);


GO

-- ----------------------------
-- Function structure for [PM_DB18]
-- ----------------------------


CREATE FUNCTION [PM_DB18](@id  int,@reginid int )
RETURNS TABLE 
RETURN 
select * from project where projecttypeid=(select ID from [projecttype] where [projecttype]='Partner') and id 
in(select projectid from projectreports pr where type='Financial' and reportreceived is null and  datediff(day,GETDATE(),reportdue)<0)
and (project.programofficer=ISNULL(@id,project.programofficer) or project.regionaldirector=ISNULL(@id,project.programofficer) or project.programassistant=ISNULL(@id,project.programofficer))
and regionid=ISNULL(@reginid,regionid);


GO

-- ----------------------------
-- Function structure for [PM_DB19]
-- ----------------------------


CREATE FUNCTION [PM_DB19](@id  int,@reginid int )
RETURNS TABLE 
RETURN 
select * from project where projecttypeid=(select ID from [projecttype] where [projecttype]='Partner') and id in(
select projectid FROM imp
WHERE (((imp.impduedate) Between getDate() And getDate()+30) AND ((imp.impperformed) Is Null)) )
and (project.programofficer=ISNULL(@id,project.programofficer) or project.regionaldirector=ISNULL(@id,project.programofficer) or project.programassistant=ISNULL(@id,project.programofficer))
and regionid=ISNULL(@reginid,regionid);

GO

-- ----------------------------
-- Function structure for [PM_DB20]
-- ----------------------------


CREATE FUNCTION [PM_DB20](@id  int,@reginid int )
RETURNS TABLE 
RETURN 
select * from project where projecttypeid=(select ID from [projecttype] where [projecttype]='Partner') and id in(
select projectid from imp where  datediff(day,GETDATE(),impduedate)<0  and impperformed is null)
and (project.programofficer=ISNULL(@id,project.programofficer) or project.regionaldirector=ISNULL(@id,project.programofficer) or project.programassistant=ISNULL(@id,project.programofficer))
and regionid=ISNULL(@reginid,regionid);


GO

-- ----------------------------
-- Function structure for [PM_DB21]
-- ----------------------------


CREATE FUNCTION [PM_DB21](@id  int,@reginid int )
RETURNS TABLE 
RETURN 
select * from project where projecttypeid=(select ID from [projecttype] where [projecttype]='Partner') and id in
(select projectid from audits WHERE (((audits.faselectionmemoduedate) Between getDate() And getDate()+30) AND ((audits.faselectionmemoreceived) Is Null)))
and (project.programofficer=ISNULL(@id,project.programofficer) or project.regionaldirector=ISNULL(@id,project.programofficer) or project.programassistant=ISNULL(@id,project.programofficer))
and regionid=ISNULL(@reginid,regionid);


GO

-- ----------------------------
-- Function structure for [PM_DB22]
-- ----------------------------


CREATE FUNCTION [PM_DB22](@id  int,@reginid int )
RETURNS TABLE 
RETURN 
select * from project where projecttypeid=(select ID from [projecttype] where [projecttype]='Partner') and id in
(select projectid from audits where faselectionmemoduedate<GETDATE() and faselectionmemoreceived is null)
and (project.programofficer=ISNULL(@id,project.programofficer) or project.regionaldirector=ISNULL(@id,project.programofficer) or project.programassistant=ISNULL(@id,project.programofficer))
and regionid=ISNULL(@reginid,regionid);


GO

-- ----------------------------
-- Function structure for [PM_DB24]
-- ----------------------------

--DB23--

CREATE FUNCTION [PM_DB24](@id  int,@reginid int )
RETURNS TABLE 
RETURN 
select * from project where projecttypeid=(select ID from [projecttype] where [projecttype]='Partner') and id in
(select projectid from audits where audits.iadue Between getDate() And getDate()+30 and iareceived is null)
and (project.programofficer=ISNULL(@id,project.programofficer) or project.regionaldirector=ISNULL(@id,project.programofficer) or project.programassistant=ISNULL(@id,project.programofficer))
and regionid=ISNULL(@reginid,regionid);

GO

-- ----------------------------
-- Function structure for [PM_DB25]
-- ----------------------------


--DB24
CREATE FUNCTION [PM_DB25](@id  int,@reginid int )
RETURNS TABLE 
RETURN 
SELECT project.*
FROM (audits INNER JOIN project ON audits.projectid = project.id) INNER JOIN projecttype ON project.projecttypeid = projecttype.id
WHERE (((audits.fadue) Between getDate() And getDate()+30) AND ((audits.fareceived) Is Null) AND ((projecttype.projecttype)='partner'))
and (project.programofficer=ISNULL(@id,project.programofficer) or project.regionaldirector=ISNULL(@id,project.programofficer) or project.programassistant=ISNULL(@id,project.programofficer))
and regionid=ISNULL(@reginid,regionid);


GO

-- ----------------------------
-- Function structure for [PM_DB26]
-- ----------------------------


--DB25
CREATE FUNCTION [PM_DB26](@id  int,@reginid int )
RETURNS TABLE 
RETURN 
SELECT project.*
	FROM (audits INNER JOIN project ON audits.projectid = project.id) INNER JOIN projecttype ON project.projecttypeid = projecttype.id
	WHERE (((audits.iadue)<getDate()) AND ((audits.iareceived) Is Null) AND ((projecttype.projecttype)='partner'))
	and (project.programofficer=ISNULL(@id,project.programofficer) or project.regionaldirector=ISNULL(@id,project.programofficer) or project.programassistant=ISNULL(@id,project.programofficer))
and regionid=ISNULL(@reginid,regionid);

GO

-- ----------------------------
-- Function structure for [PM_DB27]
-- ----------------------------


--DB26
CREATE FUNCTION [PM_DB27](@id  int,@reginid int )
RETURNS TABLE 
RETURN 
SELECT project.*
	FROM (audits INNER JOIN project ON audits.projectid = project.id) INNER JOIN projecttype ON project.projecttypeid = projecttype.id
	WHERE (((audits.fadue)<getDate()) AND ((audits.fareceived) Is Null) AND ((projecttype.projecttype)='partner'))
	and (project.programofficer=ISNULL(@id,project.programofficer) or project.regionaldirector=ISNULL(@id,project.programofficer) or project.programassistant=ISNULL(@id,project.programofficer))
and regionid=ISNULL(@reginid,regionid);


GO

-- ----------------------------
-- Function structure for [Project_Active_List]
-- ----------------------------


CREATE FUNCTION [Project_Active_List]
    (  ) 
RETURNS TABLE 
RETURN 
SELECT  * from project where  projectstatusid=(select ID from projectstatus where statusname='Active') 
and projecttypeid !=(select ID from [projecttype] where [projecttype]='Partner');


GO

-- ----------------------------
-- Function structure for [Project_DB1]
-- ----------------------------


CREATE FUNCTION [Project_DB1](@id  int,@reginid int )
RETURNS TABLE 
RETURN 
SELECT  * from project where  projectstatusid=(select ID from projectstatus where statusname='Active') 
and projecttypeid !=(select ID from [projecttype] where [projecttype]='Partner') 
and (project.programofficer=ISNULL(@id,project.programofficer) or project.regionaldirector=ISNULL(@id,project.programofficer) or project.programassistant=ISNULL(@id,project.programofficer))
and regionid=ISNULL(@reginid,regionid);

GO

-- ----------------------------
-- Function structure for [Project_DB10]
-- ----------------------------


CREATE FUNCTION [Project_DB10](@id  int,@reginid int )
RETURNS TABLE 
RETURN 
SELECT  * from project where  projectstatusid=(select ID from projectstatus where statusname='Active') 
and projecttypeid =(select ID from [projecttype] where [projecttype]='Partner') and project.managementfunds!=0 and
	(select SUM(paymentrequestamount) from paymentrequestlineitem where projectid=project.id)/project.managementfunds>=0.9
	and (project.programofficer=ISNULL(@id,project.programofficer) or project.regionaldirector=ISNULL(@id,project.programofficer) or project.programassistant=ISNULL(@id,project.programofficer))
	and regionid=ISNULL(@reginid,regionid);


GO

-- ----------------------------
-- Function structure for [Project_DB2]
-- ----------------------------



CREATE FUNCTION [Project_DB2](@id  int,@reginid int )
RETURNS TABLE 
RETURN 
SELECT  * from project where  projectstatusid=(select ID from projectstatus where statusname='Over') 
and projecttypeid !=(select ID from [projecttype] where [projecttype]='Partner')
and (project.programofficer=ISNULL(@id,project.programofficer) or project.regionaldirector=ISNULL(@id,project.programofficer) or project.programassistant=ISNULL(@id,project.programofficer))
and regionid=ISNULL(@reginid,regionid);


GO

-- ----------------------------
-- Function structure for [Project_DB3]
-- ----------------------------


CREATE FUNCTION [Project_DB3](@id  int,@reginid int )
RETURNS TABLE 
RETURN 
SELECT project.*
FROM (project INNER JOIN projecttype ON project.projecttypeid = projecttype.id) INNER JOIN projectstatus ON project.projectstatusid = projectstatus.id
WHERE ((Not (projecttype.projecttype)='partner') AND ((projectstatus.statusname)='active') AND ((project.projectenddate)>=getDate() And (project.projectenddate)<=getDate()+30))
and (project.programofficer=ISNULL(@id,project.programofficer) or project.regionaldirector=ISNULL(@id,project.programofficer) or project.programassistant=ISNULL(@id,project.programofficer))
and regionid=ISNULL(@reginid,regionid);


GO

-- ----------------------------
-- Function structure for [Project_DB4]
-- ----------------------------


CREATE FUNCTION [Project_DB4](@id  int,@reginid int )
RETURNS TABLE 
RETURN 
SELECT  * from project where  projectstatusid=(select ID from projectstatus where statusname='Active') 
and projecttypeid !=(select ID from [projecttype] where [projecttype]='Partner') and DATEDIFF(DAY,GETDATE(),projectstartdate)<=60 and
 0=(select count(*) from paymentrequestlineitem where projectid=project.id )
and (project.programofficer=ISNULL(@id,project.programofficer) or project.regionaldirector=ISNULL(@id,project.programofficer) or project.programassistant=ISNULL(@id,project.programofficer))
and regionid=ISNULL(@reginid,regionid);


GO

-- ----------------------------
-- Function structure for [Project_DB5]
-- ----------------------------



CREATE FUNCTION [Project_DB5](@id  int,@reginid int )
RETURNS TABLE 
RETURN 
select * from project where projectstatusid=(select ID from projectstatus where statusname='Active') and project.managementfunds!=0 
	and projecttypeid!=(select ID from [projecttype] where [projecttype]='Partner') and
	(select SUM(paymentrequestamount) from paymentrequestlineitem 
	where projectid=project.id)/project.managementfunds>=0.9
	and (project.programofficer=ISNULL(@id,project.programofficer) or project.regionaldirector=ISNULL(@id,project.programofficer) or project.programassistant=ISNULL(@id,project.programofficer))
	and regionid=ISNULL(@reginid,regionid);


GO

-- ----------------------------
-- Function structure for [Project_DB6]
-- ----------------------------


CREATE FUNCTION [Project_DB6](@id  int,@reginid int )
RETURNS TABLE 
RETURN 
SELECT  * from project where  projectstatusid=(select ID from projectstatus where statusname='Active') 
	and projecttypeid =(select ID from [projecttype] where [projecttype]='Partner')
	and (project.programofficer=ISNULL(@id,project.programofficer) or project.regionaldirector=ISNULL(@id,project.programofficer) or project.programassistant=ISNULL(@id,project.programofficer))
	and regionid=ISNULL(@reginid,regionid);


GO

-- ----------------------------
-- Function structure for [Project_DB7]
-- ----------------------------


CREATE FUNCTION [Project_DB7](@id  int,@reginid int )
RETURNS TABLE 
RETURN 
SELECT  * from project where  projectstatusid=(select ID from projectstatus where statusname='Over') 
and projecttypeid =(select ID from [projecttype] where [projecttype]='Partner')
	and (project.programofficer=ISNULL(@id,project.programofficer) or project.regionaldirector=ISNULL(@id,project.programofficer) or project.programassistant=ISNULL(@id,project.programofficer))
	and regionid=ISNULL(@reginid,regionid);


GO

-- ----------------------------
-- Function structure for [Project_DB8]
-- ----------------------------


CREATE FUNCTION [Project_DB8](@id  int,@reginid int )
RETURNS TABLE 
RETURN 
SELECT project.*
FROM (project INNER JOIN projecttype ON project.projecttypeid = projecttype.id) INNER JOIN projectstatus ON project.projectstatusid = projectstatus.id
WHERE (((projecttype.projecttype)='partner') AND ((projectstatus.statusname)='active') AND ((project.projectstartdate)<getDate()) AND ((project.receiptofsignedagreement) Is Null))
	and (project.programofficer=ISNULL(@id,project.programofficer) or project.regionaldirector=ISNULL(@id,project.programofficer) or project.programassistant=ISNULL(@id,project.programofficer))
	and regionid=ISNULL(@reginid,regionid);


GO

-- ----------------------------
-- Function structure for [Project_DB9]
-- ----------------------------


CREATE FUNCTION [Project_DB9](@id  int,@reginid int )
RETURNS TABLE 
RETURN 
SELECT  * from project where  projectstatusid=(select ID from projectstatus where statusname='Active') 
and projecttypeid =(select ID from [projecttype] where [projecttype]='Partner') and
	DATEDIFF(DAY,GETDATE(),projectstartdate)<=60 and 
	0=(select count(*) from paymentrequestlineitem where projectid=project.id )
    and (project.programofficer=ISNULL(@id,project.programofficer) or project.regionaldirector=ISNULL(@id,project.programofficer) or project.programassistant=ISNULL(@id,project.programofficer))
	and regionid=ISNULL(@reginid,regionid);


GO

-- ----------------------------
-- Function structure for [project_No_Payment60_List]
-- ----------------------------


CREATE FUNCTION [project_No_Payment60_List]
    (  ) 
RETURNS TABLE 
RETURN 
SELECT  * from project where  projectstatusid=(select ID from projectstatus where statusname='Active') 
and projecttypeid !=(select ID from [projecttype] where [projecttype]='Partner') and DATEDIFF(DAY,GETDATE(),projectstartdate)<=60 and
	0=(select count(*) from paymentrequestlineitem where projectid=project.id );


GO

-- ----------------------------
-- Function structure for [Project_Over_30_List]
-- ----------------------------


CREATE FUNCTION [Project_Over_30_List]
    (  ) 
RETURNS TABLE 
RETURN 
SELECT project.*
FROM (project INNER JOIN projecttype ON project.projecttypeid = projecttype.id) INNER JOIN projectstatus ON project.projectstatusid = projectstatus.id
WHERE ((Not (projecttype.projecttype)='partner') AND ((projectstatus.statusname)='active') AND ((project.projectenddate)>=getDate() And (project.projectenddate)<=getDate()+30));


GO

-- ----------------------------
-- Function structure for [Project_Over_List]
-- ----------------------------


CREATE FUNCTION [Project_Over_List]
    (  ) 
RETURNS TABLE 
RETURN 
SELECT  * from project where  projectstatusid=(select ID from projectstatus where statusname='Over') 
and projecttypeid !=(select ID from [projecttype] where [projecttype]='Partner');

GO

-- ----------------------------
-- Function structure for [Project_with_90_List]
-- ----------------------------


CREATE FUNCTION [Project_with_90_List]
    (  ) 
RETURNS TABLE 
RETURN 
---Active" Projects with more than 90% Budget Disbursed	Project – 
	--Find	Project.status = “Active” and Project.type <> “Partner Grant” and 
	--the sum total of all related payment requests divided by (/) project.management_funds >= 90%--
	select * from project where projectstatusid=(select ID from projectstatus where statusname='Active') and project.managementfunds!=0 
	and projecttypeid!=(select ID from [projecttype] where [projecttype]='Partner') and
	(select SUM(paymentrequestamount) from paymentrequestlineitem 
	where projectid=project.id)/project.managementfunds>=0.9

GO

-- ----------------------------
-- Function structure for [Projects_10_List]
-- ----------------------------


	--- Active" Partner Grants with more than 90% Budget Disbursed	Partner Grant 
	-- Find	Project.status = “Active” and Project.type = “Partner Grant” 
	--and the sum total of all related payment requests divided by (/) project.management_funds >= 90%---
	
CREATE FUNCTION [Projects_10_List]
    (  ) 
RETURNS TABLE 
RETURN 
SELECT  * from project where  projectstatusid=(select ID from projectstatus where statusname='Active') 
and projecttypeid =(select ID from [projecttype] where [projecttype]='Partner') and project.managementfunds!=0 and
	(select SUM(paymentrequestamount) from paymentrequestlineitem where projectid=project.id)/project.managementfunds>=0.9

GO

-- ----------------------------
-- Function structure for [Projects_9_List]
-- ----------------------------


-- New Partner Grants with no Payment Activity >= 60 days	Partner Grant – 
	--Find	Project.status = “Active” and Project.type = “Partner Grant” and project.start_date <= today()+60 
	--and no payment request records related to this project have been created-- 
CREATE FUNCTION [Projects_9_List]
    (  ) 
RETURNS TABLE 
RETURN 
SELECT  * from project where  projectstatusid=(select ID from projectstatus where statusname='Active') 
and projecttypeid =(select ID from [projecttype] where [projecttype]='Partner') and
	DATEDIFF(DAY,GETDATE(),projectstartdate)<=60 and 
	0=(select count(*) from paymentrequestlineitem where projectid=project.id )

GO

-- ----------------------------
-- Indexes structure for table accountcode
-- ----------------------------
CREATE INDEX [idx_accountcode_Accountcodedesc_uq] ON [accountcode]
([accountcodedesc] ASC) 
GO
CREATE INDEX [idx_accountcode_Accountcode_uq] ON [accountcode]
([accountcode] ASC) 
GO

-- ----------------------------
-- Primary Key structure for table [accountcode]
-- ----------------------------
ALTER TABLE [accountcode] ADD PRIMARY KEY NONCLUSTERED ([id])
GO

-- ----------------------------
-- Indexes structure for table actionablealerts
-- ----------------------------

-- ----------------------------
-- Primary Key structure for table [actionablealerts]
-- ----------------------------
ALTER TABLE [actionablealerts] ADD PRIMARY KEY ([id])
GO

-- ----------------------------
-- Indexes structure for table actionablealertsmessage
-- ----------------------------

-- ----------------------------
-- Primary Key structure for table [actionablealertsmessage]
-- ----------------------------
ALTER TABLE [actionablealertsmessage] ADD PRIMARY KEY ([id])
GO

-- ----------------------------
-- Indexes structure for table actionaltertchangenotifications
-- ----------------------------

-- ----------------------------
-- Primary Key structure for table [actionaltertchangenotifications]
-- ----------------------------
ALTER TABLE [actionaltertchangenotifications] ADD PRIMARY KEY NONCLUSTERED ([id])
GO

-- ----------------------------
-- Indexes structure for table actionaltertchangenotifications_useractionalertschangess
-- ----------------------------

-- ----------------------------
-- Uniques structure for table [actionaltertchangenotifications_useractionalertschangess]
-- ----------------------------
ALTER TABLE [actionaltertchangenotifications_useractionalertschangess] ADD UNIQUE ([useractionalertschangess] ASC)
GO

-- ----------------------------
-- Indexes structure for table alerttype
-- ----------------------------
CREATE UNIQUE INDEX [idx_alerttype_Typename_uq] ON [alerttype]
([typename] ASC) 
GO

-- ----------------------------
-- Primary Key structure for table [alerttype]
-- ----------------------------
ALTER TABLE [alerttype] ADD PRIMARY KEY NONCLUSTERED ([id])
GO

-- ----------------------------
-- Indexes structure for table audits
-- ----------------------------
CREATE UNIQUE INDEX [idx_audits_Projectid_uq] ON [audits]
([projectid] ASC) 
GO

-- ----------------------------
-- Primary Key structure for table [audits]
-- ----------------------------
ALTER TABLE [audits] ADD PRIMARY KEY NONCLUSTERED ([id])
GO

-- ----------------------------
-- Triggers structure for table [audits]
-- ----------------------------
CREATE TRIGGER [Audits_insert]
ON [audits]
AFTER INSERT, UPDATE
AS
begin
declare @RiskAssessment float;
declare @projectId int;
select @RiskAssessment=RiskAssessment,@projectId=projectid from inserted;
update  project set riskassessmentscore= @RiskAssessment where id=@projectid;

end;


GO

-- ----------------------------
-- Indexes structure for table Cacheactionablealertsmessage
-- ----------------------------

-- ----------------------------
-- Primary Key structure for table [Cacheactionablealertsmessage]
-- ----------------------------
ALTER TABLE [Cacheactionablealertsmessage] ADD PRIMARY KEY ([id])
GO

-- ----------------------------
-- Indexes structure for table categorylinks
-- ----------------------------

-- ----------------------------
-- Primary Key structure for table [categorylinks]
-- ----------------------------
ALTER TABLE [categorylinks] ADD PRIMARY KEY NONCLUSTERED ([id])
GO

-- ----------------------------
-- Indexes structure for table changenotificationsmessage
-- ----------------------------

-- ----------------------------
-- Primary Key structure for table [changenotificationsmessage]
-- ----------------------------
ALTER TABLE [changenotificationsmessage] ADD PRIMARY KEY ([id])
GO

-- ----------------------------
-- Indexes structure for table changenotificationtype
-- ----------------------------

-- ----------------------------
-- Primary Key structure for table [changenotificationtype]
-- ----------------------------
ALTER TABLE [changenotificationtype] ADD PRIMARY KEY NONCLUSTERED ([id])
GO

-- ----------------------------
-- Indexes structure for table country
-- ----------------------------
CREATE UNIQUE INDEX [idx_country_Countryname_uq] ON [country]
([countryname] ASC) 
GO
CREATE INDEX [idx_country_Regionid] ON [country]
([regionid] ASC) 
GO
CREATE INDEX [idx_country_Programassistant] ON [country]
([programassistant] ASC) 
GO
CREATE INDEX [idx_country_Programofficer] ON [country]
([programofficer] ASC) 
GO

-- ----------------------------
-- Primary Key structure for table [country]
-- ----------------------------
ALTER TABLE [country] ADD PRIMARY KEY NONCLUSTERED ([id])
GO

-- ----------------------------
-- Indexes structure for table dashboardlink
-- ----------------------------

-- ----------------------------
-- Primary Key structure for table [dashboardlink]
-- ----------------------------
ALTER TABLE [dashboardlink] ADD PRIMARY KEY ([id])
GO

-- ----------------------------
-- Indexes structure for table donor
-- ----------------------------

-- ----------------------------
-- Primary Key structure for table [donor]
-- ----------------------------
ALTER TABLE [donor] ADD PRIMARY KEY NONCLUSTERED ([id])
GO

-- ----------------------------
-- Indexes structure for table donoraward
-- ----------------------------
CREATE UNIQUE INDEX [idx_Donoraward_Donorawardnumber_uq] ON [donoraward]
([donorawardnumber] ASC) 
GO
CREATE INDEX [idx_Donoraward_Donorid] ON [donoraward]
([donorid] ASC) 
GO
CREATE INDEX [idx_Donoraward_Donorawardtypeid] ON [donoraward]
([donorawardtypeid] ASC) 
GO
CREATE INDEX [idx_Donoraward_Donorawardstatusid] ON [donoraward]
([donorawardstatusid] ASC) 
GO

-- ----------------------------
-- Primary Key structure for table [donoraward]
-- ----------------------------
ALTER TABLE [donoraward] ADD PRIMARY KEY NONCLUSTERED ([id])
GO

-- ----------------------------
-- Uniques structure for table [donoraward]
-- ----------------------------
ALTER TABLE [donoraward] ADD UNIQUE ([donorawardnumber] ASC)
GO

-- ----------------------------
-- Indexes structure for table donorawardmodification
-- ----------------------------

-- ----------------------------
-- Primary Key structure for table [donorawardmodification]
-- ----------------------------
ALTER TABLE [donorawardmodification] ADD PRIMARY KEY NONCLUSTERED ([id])
GO

-- ----------------------------
-- Indexes structure for table donorawardstatus
-- ----------------------------

-- ----------------------------
-- Primary Key structure for table [donorawardstatus]
-- ----------------------------
ALTER TABLE [donorawardstatus] ADD PRIMARY KEY NONCLUSTERED ([id])
GO

-- ----------------------------
-- Indexes structure for table donorawardstatustransitionsallowed
-- ----------------------------

-- ----------------------------
-- Primary Key structure for table [donorawardstatustransitionsallowed]
-- ----------------------------
ALTER TABLE [donorawardstatustransitionsallowed] ADD PRIMARY KEY NONCLUSTERED ([id])
GO

-- ----------------------------
-- Indexes structure for table donorawardtype
-- ----------------------------
CREATE UNIQUE INDEX [idx_donorawardtype_Donorawardtypedesc_uq] ON [donorawardtype]
([donorawardtypedesc] ASC) 
GO
CREATE UNIQUE INDEX [idx_donorawardtype_Donorawardtype_uq] ON [donorawardtype]
([donorawardtype] ASC) 
GO

-- ----------------------------
-- Primary Key structure for table [donorawardtype]
-- ----------------------------
ALTER TABLE [donorawardtype] ADD PRIMARY KEY NONCLUSTERED ([id])
GO

-- ----------------------------
-- Indexes structure for table fieldoffice
-- ----------------------------

-- ----------------------------
-- Primary Key structure for table [fieldoffice]
-- ----------------------------
ALTER TABLE [fieldoffice] ADD PRIMARY KEY NONCLUSTERED ([id])
GO

-- ----------------------------
-- Indexes structure for table imp
-- ----------------------------

-- ----------------------------
-- Primary Key structure for table [imp]
-- ----------------------------
ALTER TABLE [imp] ADD PRIMARY KEY NONCLUSTERED ([id])
GO

-- ----------------------------
-- Indexes structure for table keyword
-- ----------------------------
CREATE UNIQUE INDEX [idx_keyword_Keywordcode_uq] ON [keyword]
([keywordcode] ASC) 
GO

-- ----------------------------
-- Primary Key structure for table [keyword]
-- ----------------------------
ALTER TABLE [keyword] ADD PRIMARY KEY NONCLUSTERED ([id])
GO

-- ----------------------------
-- Indexes structure for table link
-- ----------------------------

-- ----------------------------
-- Primary Key structure for table [link]
-- ----------------------------
ALTER TABLE [link] ADD PRIMARY KEY NONCLUSTERED ([id])
GO

-- ----------------------------
-- Indexes structure for table linkcategory
-- ----------------------------

-- ----------------------------
-- Primary Key structure for table [linkcategory]
-- ----------------------------
ALTER TABLE [linkcategory] ADD PRIMARY KEY NONCLUSTERED ([id])
GO

-- ----------------------------
-- Indexes structure for table modificationtype
-- ----------------------------

-- ----------------------------
-- Primary Key structure for table [modificationtype]
-- ----------------------------
ALTER TABLE [modificationtype] ADD PRIMARY KEY NONCLUSTERED ([id])
GO

-- ----------------------------
-- Indexes structure for table organizationtype
-- ----------------------------

-- ----------------------------
-- Primary Key structure for table [organizationtype]
-- ----------------------------
ALTER TABLE [organizationtype] ADD PRIMARY KEY NONCLUSTERED ([id])
GO

-- ----------------------------
-- Indexes structure for table partner
-- ----------------------------
CREATE INDEX [idx_partner_Countryid] ON [partner]
([countryid] ASC) 
GO

-- ----------------------------
-- Primary Key structure for table [partner]
-- ----------------------------
ALTER TABLE [partner] ADD PRIMARY KEY NONCLUSTERED ([id])
GO

-- ----------------------------
-- Triggers structure for table [partner]
-- ----------------------------
CREATE TRIGGER [payeeInsert]
ON [partner]
AFTER INSERT
AS
begin
DECLARE @name varchar(255);
DECLARE @countno int;
select @name=partnername from inserted;
select @countno=count(*) from payee where payee.payeename like '%'+@name+'%'
if @countno =1
begin
update payee set payeetype='Partner' where payeename like '%'+@name+'%';
end
else if @countno=0
begin
insert into payee(payeename,payeetype) values (@name,'Partner');
end
;
end;



GO

-- ----------------------------
-- Indexes structure for table payee
-- ----------------------------

-- ----------------------------
-- Primary Key structure for table [payee]
-- ----------------------------
ALTER TABLE [payee] ADD PRIMARY KEY NONCLUSTERED ([id])
GO

-- ----------------------------
-- Indexes structure for table paymentrequest
-- ----------------------------
CREATE INDEX [idx_paymentrequest_Createdby] ON [paymentrequest]
([createdby] ASC) 
GO
CREATE INDEX [idx_paymentrequest_Payeeid] ON [paymentrequest]
([payeeid] ASC) 
GO
CREATE INDEX [idx_paymentrequest_Statuscode] ON [paymentrequest]
([statuscode] ASC) 
GO
CREATE INDEX [idx_paymentrequest_Amount] ON [paymentrequest]
([amount] ASC) 
GO
CREATE INDEX [idx_paymentrequest_Createdate] ON [paymentrequest]
([createdate] ASC) 
GO
CREATE INDEX [idx_paymentrequest_Paymenttypeid] ON [paymentrequest]
([paymenttypeid] ASC) 
GO

-- ----------------------------
-- Primary Key structure for table [paymentrequest]
-- ----------------------------
ALTER TABLE [paymentrequest] ADD PRIMARY KEY NONCLUSTERED ([id])
GO

-- ----------------------------
-- Indexes structure for table paymentrequestlineitem
-- ----------------------------
CREATE INDEX [idx_paymentrequestlineitem_Paymentrequestamount] ON [paymentrequestlineitem]
([paymentrequestamount] ASC) 
GO
CREATE INDEX [idx_paymentrequestlineitem_Paymentrequestid] ON [paymentrequestlineitem]
([paymentrequestid] ASC) 
GO
CREATE INDEX [idx_paymentrequestlineitem_Accountcodeid] ON [paymentrequestlineitem]
([accountcodeid] ASC) 
GO
CREATE INDEX [idx_paymentrequestlineitem_Projectid] ON [paymentrequestlineitem]
([projectid] ASC) 
GO

-- ----------------------------
-- Primary Key structure for table [paymentrequestlineitem]
-- ----------------------------
ALTER TABLE [paymentrequestlineitem] ADD PRIMARY KEY NONCLUSTERED ([id])
GO

-- ----------------------------
-- Indexes structure for table paymentrequestmodification
-- ----------------------------

-- ----------------------------
-- Primary Key structure for table [paymentrequestmodification]
-- ----------------------------
ALTER TABLE [paymentrequestmodification] ADD PRIMARY KEY NONCLUSTERED ([id])
GO

-- ----------------------------
-- Indexes structure for table paymentrequeststatus
-- ----------------------------

-- ----------------------------
-- Primary Key structure for table [paymentrequeststatus]
-- ----------------------------
ALTER TABLE [paymentrequeststatus] ADD PRIMARY KEY NONCLUSTERED ([id])
GO

-- ----------------------------
-- Indexes structure for table paymentrequeststatustransitions
-- ----------------------------

-- ----------------------------
-- Primary Key structure for table [paymentrequeststatustransitions]
-- ----------------------------
ALTER TABLE [paymentrequeststatustransitions] ADD PRIMARY KEY NONCLUSTERED ([id])
GO

-- ----------------------------
-- Indexes structure for table paymentstatustype
-- ----------------------------

-- ----------------------------
-- Primary Key structure for table [paymentstatustype]
-- ----------------------------
ALTER TABLE [paymentstatustype] ADD PRIMARY KEY NONCLUSTERED ([id])
GO

-- ----------------------------
-- Indexes structure for table paymenttype
-- ----------------------------
CREATE UNIQUE INDEX [idx_paymenttype_Paymenttypename_uq] ON [paymenttype]
([paymenttypename] ASC) 
GO

-- ----------------------------
-- Primary Key structure for table [paymenttype]
-- ----------------------------
ALTER TABLE [paymenttype] ADD PRIMARY KEY NONCLUSTERED ([id])
GO

-- ----------------------------
-- Indexes structure for table project
-- ----------------------------
CREATE INDEX [idx_project_Acronym] ON [project]
([acronym] ASC) 
GO
CREATE INDEX [idx_project_Projecttitle] ON [project]
([projecttitle] ASC) 
GO
CREATE INDEX [idx_project_Partnerid] ON [project]
([partnerid] ASC) 
GO
CREATE INDEX [idx_project_Projecttypeid] ON [project]
([projecttypeid] ASC) 
GO
CREATE INDEX [idx_project_Regionid] ON [project]
([regionid] ASC) 
GO
CREATE INDEX [idx_project_Countryid] ON [project]
([countryid] ASC) 
GO
CREATE INDEX [idx_project_Projectstatusid] ON [project]
([projectstatusid] ASC) 
GO
CREATE INDEX [idx_project_Donorawardid] ON [project]
([donorawardid] ASC) 
GO
CREATE INDEX [idx_project_Programofficer] ON [project]
([programofficer] ASC) 
GO
CREATE INDEX [idx_project_Managementfunds] ON [project]
([managementfunds] ASC) 
GO
CREATE INDEX [idx_project_partnergrantfunds] ON [project]
([partnergrantfunds] ASC) 
GO
CREATE INDEX [idx_project_Regionaldirector] ON [project]
([regionaldirector] ASC) 
GO
CREATE INDEX [idx_project_programassistant] ON [project]
([programassistant] ASC) 
GO
CREATE INDEX [idx_project_Parentprojectid] ON [project]
([parentprojectid] ASC) 
GO
CREATE INDEX [idx_project_Masterprojectid] ON [project]
([masterprojectid] ASC) 
GO
CREATE INDEX [idx_project_Costcenternumber] ON [project]
([costcenternumber] ASC) 
GO

-- ----------------------------
-- Primary Key structure for table [project]
-- ----------------------------
ALTER TABLE [project] ADD PRIMARY KEY NONCLUSTERED ([id])
GO

-- ----------------------------
-- Triggers structure for table [project]
-- ----------------------------
CREATE TRIGGER [cn10]
ON [project]
AFTER UPDATE
AS
begin
--CN10 - Grant Status Change	Partner Grant – Summary	Status' changed from one value to another 
--value and ‘Type’ = “Partner Grant”	
--The end date of Grant # <project id> for Partner <Partner Name> was changed from <previous status> to <status> on 
--<lastupdatedate>.
DECLARE @message varchar(255);
DECLARE @projectid varchar(100);
DECLARE @id int;
DECLARE @name varchar(100);
Declare @oldsttusname varchar(100);
DECLARE @newstatusname varchar(100);
DECLARE @oid int;
DECLARE @nid int;


IF EXISTS (SELECT *FROM INSERTED I JOIN DELETED D ON I.id = D.id 
					WHERE I.projectstatusid<>D.projectstatusid 
					and I.projecttypeid=(select ID from [projecttype] where [projecttype]='Partner'))
	begin
		select @id=id,@projectid=projectid,@nid=projectstatusid,@name=(select partnername from partner where id=partnerid) from INSERTED;
		select @oid=projectstatusid from deleted;
		select @oldsttusname=statusname  from projectstatus where id=@oid;
		select @newstatusname=statusname  from projectstatus where id=@nid;
		set @message='The status of Grant #'+CAST(@projectid as varchar(100))+' for Partner '+ @name +
		' was changed from '+ @oldsttusname +' to '+@newstatusname+' on '+ convert(varchar,GETDATE(),1);
		insert into changenotificationsmessage(message,date,url,changenotificationsid) values(@message,GETDATE(),'showEditPartnergrantView.do?id='+cast(@id as varchar(20)),10);
	end

end;


GO
CREATE TRIGGER [cn3]
ON [project]
AFTER UPDATE
AS
begin
--CN3 Project Management Funds Changed	Projects – Summary	
--Management Funds' is changed from one value to another value and ‘Type’ <> “Project	
--The management funds of Project # <Project ID> was changed from $<previous management funds> to $<management funds> 
--on <lastupdatedate>.
DECLARE @message varchar(255);
DECLARE @projectid varchar(100);
DECLARE @id int;
DECLARE @name varchar(100);
Declare @oldfunds numeric(15,2);
DECLARE @newfunds numeric(15,2);

IF EXISTS (SELECT *FROM INSERTED I JOIN DELETED D ON I.id = D.id 
					WHERE i.managementfunds<>d.managementfunds 
					and i.projecttypeid!=(select ID from [projecttype] where [projecttype]='Partner'))
	begin
		select @id=id,@projectid=projectid,@newfunds=managementfunds  from INSERTED;
		select @oldfunds=managementfunds from deleted;
		
		set @message='The management funds of Project #'+CAST(@projectid as varchar(100))+
		' was changed from '+ PARSENAME('$'+ Convert(varchar,Convert(money,@oldfunds),1),2) +' to '+PARSENAME('$'+ Convert(varchar,Convert(money,@newfunds),1),2)+' on '+ convert(varchar,GETDATE(),1);
		insert into changenotificationsmessage(message,date,url,changenotificationsid) values(@message,GETDATE(),'showEditCipeprojectDetail.do?id='+cast(@id as varchar(20)),3);
	end

end;


GO
CREATE TRIGGER [cn4]
ON [project]
AFTER UPDATE
AS
begin
--CN4 - Partner Grants Funds Changed	Partner Grant – Summary	
--Partner Grant Funds' is changed from one value to another value and ‘Type’ = “Partner Grant”	
--The Partner Grant Funds of Grant # <Project ID> for Partner <Partner Name> was changed 
--from $<previous partner grant funds> to $<partner grant funds> on <lastupdatedate>.
DECLARE @message varchar(255);
DECLARE @projectid varchar(100);
DECLARE @id int;
DECLARE @name varchar(100);
Declare @oldfunds numeric(15,2);
DECLARE @newfunds numeric(15,2);

IF EXISTS (SELECT *FROM INSERTED I JOIN DELETED D ON I.id = D.id 
					WHERE i.partnergrantfunds<>d.partnergrantfunds 
					and i.projecttypeid=(select ID from [projecttype] where [projecttype]='Partner'))
	begin
		select @id=id,@projectid=projectid,@newfunds=partnergrantfunds,@name=(select partnername from partner where id=partnerid)  
		from INSERTED;
		select @oldfunds=partnergrantfunds from deleted;
		
		set @message='The Partner Grant Funds of Grant #'+CAST(@projectid as varchar(100))+' for Partner '+@name+
		' was changed from '+ PARSENAME('$'+ Convert(varchar,Convert(money,@oldfunds),1),2) +' to '+PARSENAME('$'+ Convert(varchar,Convert(money,@newfunds),1),2) +' on '+ convert(varchar,GETDATE(),1);
		insert into changenotificationsmessage(message,date,url,changenotificationsid) values(@message,GETDATE(),'showEditPartnergrantView.do?id='+cast(@id as varchar(20)),4);
	end

end;


GO
CREATE TRIGGER [cn5]
ON [project]
AFTER UPDATE
AS
begin
DECLARE @message varchar(255);
DECLARE @projectid varchar(100);
DECLARE @id int;
DECLARE @name varchar(100);
Declare @olddate datetime;
DECLARE @newdate datetime;

IF EXISTS (SELECT *FROM INSERTED I JOIN DELETED D ON I.id = D.id 
					WHERE I.projectstartdate<>D.projectstartdate 
					and I.projecttypeid!=(select ID from [projecttype] where [projecttype]='Partner'))
	begin
		select @id=id,@projectid=projectid,@newdate=projectstartdate from INSERTED;
		select @olddate=projectstartdate from deleted;
		
		set @message='The start date of Grant #'+CAST(@projectid as varchar(100))+
		' was changed from '+ convert(varchar,@olddate ,1) +' to '+convert(varchar,@newdate,1)+' on '+ convert(varchar,GETDATE(),1);
		insert into changenotificationsmessage(message,date,url,changenotificationsid) values(@message,GETDATE(),'showEditCipeprojectDetail.do?id='+cast(@id as varchar(20)),5);
	end

end;



GO
CREATE TRIGGER [cn6]
ON [project]
AFTER UPDATE
AS
begin
--CN6 - Project End Date Changed	Projects – Summary	
--End_Date' changed from one value to another value and ‘Type’ <> “Project	
--The end date of Project # <project id> was changed from <previous start date> to <start date> on <lastupdatedate>.
DECLARE @message varchar(255);
DECLARE @projectid varchar(100);
DECLARE @id int;

DECLARE @name varchar(100);
Declare @olddate datetime;
DECLARE @newdate datetime;

IF EXISTS (SELECT *FROM INSERTED I JOIN DELETED D ON I.id = D.id 
					WHERE I.projectenddate<>D.projectenddate 
					and I.projecttypeid!=(select ID from [projecttype] where [projecttype]='Partner'))
	begin
		select @id=id,@projectid=projectid,@newdate=projectenddate from INSERTED;
		select @olddate=projectenddate from deleted;
		
		set @message='The end date of Grant #'+CAST(@projectid as varchar(100))+
		' was changed from '+ convert(varchar,@olddate ,1) +' to '+convert(varchar,@newdate,1)+' on '+ convert(varchar,GETDATE(),1);
		insert into changenotificationsmessage(message,date,url,changenotificationsid) values(@message,GETDATE(),'showEditCipeprojectDetail.do?id='+cast(@id as varchar(20)),6);
	end

end;


GO
CREATE TRIGGER [cn7]
ON [project]
AFTER UPDATE
AS
begin
DECLARE @message varchar(255);
DECLARE @projectid varchar(100);
DECLARE @id int;
DECLARE @name varchar(100);
Declare @olddate datetime;
DECLARE @newdate datetime;

IF EXISTS (SELECT *FROM INSERTED I JOIN DELETED D ON I.id = D.id 
					WHERE I.projectstartdate<>D.projectstartdate 
					and I.projecttypeid=(select ID from [projecttype] where [projecttype]='Partner'))
	begin
		select @id=id,@projectid=projectid,@newdate=projectstartdate,@name=(select partnername from partner where id=partnerid)  
		from INSERTED;
		select @olddate=projectstartdate from deleted;
		
		set @message='The start date of Grant #'+@projectid +' for Partner '+@name+
		' was changed from '+ convert(varchar,@olddate ,1) +' to '+convert(varchar,@newdate,1)+' on '+ convert(varchar,GETDATE(),1);
		insert into changenotificationsmessage(message,date,url,changenotificationsid) values(@message,GETDATE(),'showEditPartnergrantView.do?id='+cast(@id as varchar(20)),7);
	end

end;


GO
CREATE TRIGGER [cn8]
ON [project]
AFTER UPDATE
AS
begin
DECLARE @message varchar(255);
DECLARE @projectid varchar(100);
DECLARE @id int;

DECLARE @name varchar(100);
Declare @olddate datetime;
DECLARE @newdate datetime;

IF EXISTS (SELECT *FROM INSERTED I JOIN DELETED D ON I.id = D.id 
					WHERE I.projectenddate<>D.projectenddate 
					and I.projecttypeid=(select ID from [projecttype] where [projecttype]='Partner'))
	begin
		select @id=id,@projectid=projectid,@newdate=projectenddate,@name=(select partnername from partner where id=partnerid)  
		from INSERTED;
		select @olddate=projectenddate from deleted;
		
		set @message='The end date of Grant #'+CAST(@projectid as varchar(100))+' for Partner '+@name+
		' was changed from '+ convert(varchar,@olddate ,1) +' to '+convert(varchar,@newdate,1)+' on '+ convert(varchar,GETDATE(),1);
		insert into changenotificationsmessage(message,date,url,changenotificationsid) values(@message,GETDATE(),'showEditPartnergrantView.do?id='+cast(@id as varchar(20)),8);
		
	end

end;


GO
CREATE TRIGGER [cn9]
ON [project]
AFTER UPDATE
AS
begin
--CN9 - Project Status Change	Projects – Summary	Status' changed from one value to another value 
--and ‘Type’ <> “ProjectThe status of Project # <project id> was changed 
--from <previous status> to <status> on <lastupdatedate>.
DECLARE @message varchar(255);
DECLARE @projectid varchar(100);
DECLARE @id int;
DECLARE @name varchar(100);
Declare @oldsttusname varchar(100);
DECLARE @newstatusname varchar(100);
DECLARE @oid int;
DECLARE @nid int;


IF EXISTS (SELECT *FROM INSERTED I JOIN DELETED D ON I.id = D.id 
					WHERE I.projectstatusid<>D.projectstatusid 
					and I.projecttypeid!=(select ID from [projecttype] where [projecttype]='Partner'))
	begin
		select @id=id,@projectid=projectid,@nid=projectstatusid from INSERTED;
		select @oid=projectstatusid from deleted;
		select @oldsttusname=statusname  from projectstatus where id=@oid;
		select @newstatusname=statusname  from projectstatus where id=@nid;
		set @message='The status of Project #'+CAST(@projectid as varchar(100))+
		' was changed from '+ @oldsttusname +' to '+@newstatusname+' on '+ convert(varchar,GETDATE(),1);
		insert into changenotificationsmessage(message,date,url,changenotificationsid) values(@message,GETDATE(),'showEditCipeprojectDetail.do?id='+cast(@id as varchar(20)),9);
	end

end;


GO

-- ----------------------------
-- Indexes structure for table projectkeyword
-- ----------------------------
CREATE UNIQUE INDEX [idx_projectkeyword_projectid_uq] ON [projectkeyword]
([projectid] ASC, [keywordid] ASC) 
GO

-- ----------------------------
-- Primary Key structure for table [projectkeyword]
-- ----------------------------
ALTER TABLE [projectkeyword] ADD PRIMARY KEY NONCLUSTERED ([id])
GO

-- ----------------------------
-- Indexes structure for table projectmodification
-- ----------------------------

-- ----------------------------
-- Primary Key structure for table [projectmodification]
-- ----------------------------
ALTER TABLE [projectmodification] ADD PRIMARY KEY NONCLUSTERED ([id])
GO

-- ----------------------------
-- Indexes structure for table projectreports
-- ----------------------------
CREATE INDEX [idx_projectreports_Projectid] ON [projectreports]
([projectid] ASC) 
GO
CREATE INDEX [idx_projectreports_Type] ON [projectreports]
([type] ASC) 
GO

-- ----------------------------
-- Primary Key structure for table [projectreports]
-- ----------------------------
ALTER TABLE [projectreports] ADD PRIMARY KEY NONCLUSTERED ([id])
GO

-- ----------------------------
-- Indexes structure for table projectstatus
-- ----------------------------
CREATE UNIQUE INDEX [idx_projectstatus_Statusname_uq] ON [projectstatus]
([statusname] ASC) 
GO
CREATE UNIQUE INDEX [idx_projectstatus_Statusdesc_uq] ON [projectstatus]
([statusdesc] ASC) 
GO

-- ----------------------------
-- Primary Key structure for table [projectstatus]
-- ----------------------------
ALTER TABLE [projectstatus] ADD PRIMARY KEY NONCLUSTERED ([id])
GO

-- ----------------------------
-- Indexes structure for table projectstatuschangelog
-- ----------------------------

-- ----------------------------
-- Primary Key structure for table [projectstatuschangelog]
-- ----------------------------
ALTER TABLE [projectstatuschangelog] ADD PRIMARY KEY NONCLUSTERED ([id])
GO

-- ----------------------------
-- Indexes structure for table projectstatustransitionsallowed
-- ----------------------------

-- ----------------------------
-- Primary Key structure for table [projectstatustransitionsallowed]
-- ----------------------------
ALTER TABLE [projectstatustransitionsallowed] ADD PRIMARY KEY NONCLUSTERED ([id])
GO

-- ----------------------------
-- Indexes structure for table projecttype
-- ----------------------------

-- ----------------------------
-- Primary Key structure for table [projecttype]
-- ----------------------------
ALTER TABLE [projecttype] ADD PRIMARY KEY NONCLUSTERED ([id])
GO

-- ----------------------------
-- Indexes structure for table region
-- ----------------------------
CREATE UNIQUE INDEX [idx_region_Regionname_uq] ON [region]
([regionname] ASC) 
GO
CREATE INDEX [idx_region_Regionaldirector] ON [region]
([regionaldirector] ASC) 
GO

-- ----------------------------
-- Primary Key structure for table [region]
-- ----------------------------
ALTER TABLE [region] ADD PRIMARY KEY NONCLUSTERED ([id])
GO

-- ----------------------------
-- Indexes structure for table role
-- ----------------------------

-- ----------------------------
-- Primary Key structure for table [role]
-- ----------------------------
ALTER TABLE [role] ADD PRIMARY KEY NONCLUSTERED ([id])
GO

-- ----------------------------
-- Indexes structure for table roleprivileges
-- ----------------------------

-- ----------------------------
-- Primary Key structure for table [roleprivileges]
-- ----------------------------
ALTER TABLE [roleprivileges] ADD PRIMARY KEY NONCLUSTERED ([id])
GO

-- ----------------------------
-- Indexes structure for table stateprovince
-- ----------------------------

-- ----------------------------
-- Primary Key structure for table [stateprovince]
-- ----------------------------
ALTER TABLE [stateprovince] ADD PRIMARY KEY NONCLUSTERED ([id])
GO

-- ----------------------------
-- Indexes structure for table userinfo
-- ----------------------------

-- ----------------------------
-- Primary Key structure for table [userinfo]
-- ----------------------------
ALTER TABLE [userinfo] ADD PRIMARY KEY NONCLUSTERED ([id])
GO

-- ----------------------------
-- Indexes structure for table userpreference
-- ----------------------------

-- ----------------------------
-- Primary Key structure for table [userpreference]
-- ----------------------------
ALTER TABLE [userpreference] ADD PRIMARY KEY ([id])
GO

-- ----------------------------
-- Indexes structure for table userrole
-- ----------------------------

-- ----------------------------
-- Primary Key structure for table [userrole]
-- ----------------------------
ALTER TABLE [userrole] ADD PRIMARY KEY NONCLUSTERED ([id])
GO

-- ----------------------------
-- Indexes structure for table usertype
-- ----------------------------

-- ----------------------------
-- Primary Key structure for table [usertype]
-- ----------------------------
ALTER TABLE [usertype] ADD PRIMARY KEY NONCLUSTERED ([id])
GO

-- ----------------------------
-- Foreign Key structure for table [accountcode]
-- ----------------------------
ALTER TABLE [accountcode] ADD FOREIGN KEY ([createdby]) REFERENCES [userinfo] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [accountcode] ADD FOREIGN KEY ([lastupdatedby]) REFERENCES [userinfo] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO

-- ----------------------------
-- Foreign Key structure for table [actionablealerts]
-- ----------------------------
ALTER TABLE [actionablealerts] ADD FOREIGN KEY ([createdby]) REFERENCES [userinfo] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [actionablealerts] ADD FOREIGN KEY ([lastupdatedby]) REFERENCES [userinfo] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO

-- ----------------------------
-- Foreign Key structure for table [actionablealertsmessage]
-- ----------------------------
ALTER TABLE [actionablealertsmessage] ADD FOREIGN KEY ([actionablealertsid]) REFERENCES [actionablealerts] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [actionablealertsmessage] ADD FOREIGN KEY ([regionid]) REFERENCES [region] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [actionablealertsmessage] ADD FOREIGN KEY ([programofficer]) REFERENCES [userinfo] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [actionablealertsmessage] ADD FOREIGN KEY ([regionaldirector]) REFERENCES [userinfo] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [actionablealertsmessage] ADD FOREIGN KEY ([programassistant]) REFERENCES [userinfo] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO

-- ----------------------------
-- Foreign Key structure for table [actionaltertchangenotifications]
-- ----------------------------
ALTER TABLE [actionaltertchangenotifications] ADD FOREIGN KEY ([alerttypeid]) REFERENCES [alerttype] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [actionaltertchangenotifications] ADD FOREIGN KEY ([changetypeid]) REFERENCES [changenotificationtype] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [actionaltertchangenotifications] ADD FOREIGN KEY ([createdby]) REFERENCES [userinfo] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [actionaltertchangenotifications] ADD FOREIGN KEY ([lastupdatedby]) REFERENCES [userinfo] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO

-- ----------------------------
-- Foreign Key structure for table [actionaltertchangenotifications_useractionalertschangess]
-- ----------------------------
ALTER TABLE [actionaltertchangenotifications_useractionalertschangess] ADD FOREIGN KEY ([actionaltertchangenotifications]) REFERENCES [actionaltertchangenotifications] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO

-- ----------------------------
-- Foreign Key structure for table [alerttype]
-- ----------------------------
ALTER TABLE [alerttype] ADD FOREIGN KEY ([createdby]) REFERENCES [userinfo] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [alerttype] ADD FOREIGN KEY ([lastupdatedby]) REFERENCES [userinfo] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO

-- ----------------------------
-- Foreign Key structure for table [audits]
-- ----------------------------
ALTER TABLE [audits] ADD FOREIGN KEY ([createdby]) REFERENCES [userinfo] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [audits] ADD FOREIGN KEY ([lastupdatedby]) REFERENCES [userinfo] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [audits] ADD FOREIGN KEY ([projectid]) REFERENCES [project] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO

-- ----------------------------
-- Foreign Key structure for table [Cacheactionablealertsmessage]
-- ----------------------------
ALTER TABLE [Cacheactionablealertsmessage] ADD FOREIGN KEY ([actionablealertsid]) REFERENCES [actionablealerts] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [Cacheactionablealertsmessage] ADD FOREIGN KEY ([regionid]) REFERENCES [region] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [Cacheactionablealertsmessage] ADD FOREIGN KEY ([programofficer]) REFERENCES [userinfo] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [Cacheactionablealertsmessage] ADD FOREIGN KEY ([regionaldirector]) REFERENCES [userinfo] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [Cacheactionablealertsmessage] ADD FOREIGN KEY ([programassistant]) REFERENCES [userinfo] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO

-- ----------------------------
-- Foreign Key structure for table [categorylinks]
-- ----------------------------
ALTER TABLE [categorylinks] ADD FOREIGN KEY ([linkcategoryid]) REFERENCES [linkcategory] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [categorylinks] ADD FOREIGN KEY ([linkid]) REFERENCES [link] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO

-- ----------------------------
-- Foreign Key structure for table [changenotificationsmessage]
-- ----------------------------
ALTER TABLE [changenotificationsmessage] ADD FOREIGN KEY ([changenotificationsid]) REFERENCES [changenotificationtype] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [changenotificationsmessage] ADD FOREIGN KEY ([regionid]) REFERENCES [region] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [changenotificationsmessage] ADD FOREIGN KEY ([programofficer]) REFERENCES [userinfo] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [changenotificationsmessage] ADD FOREIGN KEY ([regionaldirector]) REFERENCES [userinfo] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [changenotificationsmessage] ADD FOREIGN KEY ([programassistant]) REFERENCES [userinfo] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO

-- ----------------------------
-- Foreign Key structure for table [changenotificationtype]
-- ----------------------------
ALTER TABLE [changenotificationtype] ADD FOREIGN KEY ([createdby]) REFERENCES [userinfo] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [changenotificationtype] ADD FOREIGN KEY ([lastupdatedby]) REFERENCES [userinfo] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO

-- ----------------------------
-- Foreign Key structure for table [country]
-- ----------------------------
ALTER TABLE [country] ADD FOREIGN KEY ([createdby]) REFERENCES [userinfo] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [country] ADD FOREIGN KEY ([lastupdatedby]) REFERENCES [userinfo] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [country] ADD FOREIGN KEY ([programassistant]) REFERENCES [userinfo] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [country] ADD FOREIGN KEY ([programofficer]) REFERENCES [userinfo] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [country] ADD FOREIGN KEY ([programassistant]) REFERENCES [userinfo] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [country] ADD FOREIGN KEY ([programofficer]) REFERENCES [userinfo] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [country] ADD FOREIGN KEY ([regionid]) REFERENCES [region] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [country] ADD FOREIGN KEY ([regionid]) REFERENCES [region] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO

-- ----------------------------
-- Foreign Key structure for table [dashboardlink]
-- ----------------------------
ALTER TABLE [dashboardlink] ADD FOREIGN KEY ([createdby]) REFERENCES [userinfo] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [dashboardlink] ADD FOREIGN KEY ([lastupdatedby]) REFERENCES [userinfo] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO

-- ----------------------------
-- Foreign Key structure for table [donor]
-- ----------------------------
ALTER TABLE [donor] ADD FOREIGN KEY ([countryid]) REFERENCES [country] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [donor] ADD FOREIGN KEY ([countryid]) REFERENCES [country] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [donor] ADD FOREIGN KEY ([createdby]) REFERENCES [userinfo] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [donor] ADD FOREIGN KEY ([lastupdatedby]) REFERENCES [userinfo] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [donor] ADD FOREIGN KEY ([state]) REFERENCES [stateprovince] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO

-- ----------------------------
-- Foreign Key structure for table [donoraward]
-- ----------------------------
ALTER TABLE [donoraward] ADD FOREIGN KEY ([createdby]) REFERENCES [userinfo] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [donoraward] ADD FOREIGN KEY ([donorawardtypeid]) REFERENCES [donorawardtype] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [donoraward] ADD FOREIGN KEY ([donorid]) REFERENCES [donor] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [donoraward] ADD FOREIGN KEY ([donorawardstatusid]) REFERENCES [donorawardstatus] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [donoraward] ADD FOREIGN KEY ([donorid]) REFERENCES [donor] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [donoraward] ADD FOREIGN KEY ([donorawardstatusid]) REFERENCES [donorawardstatus] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [donoraward] ADD FOREIGN KEY ([donorawardtypeid]) REFERENCES [donorawardtype] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [donoraward] ADD FOREIGN KEY ([lastupdatedby]) REFERENCES [userinfo] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO

-- ----------------------------
-- Foreign Key structure for table [donorawardmodification]
-- ----------------------------
ALTER TABLE [donorawardmodification] ADD FOREIGN KEY ([createdby]) REFERENCES [userinfo] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [donorawardmodification] ADD FOREIGN KEY ([donorawardid]) REFERENCES [donoraward] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [donorawardmodification] ADD FOREIGN KEY ([lastupdatedby]) REFERENCES [userinfo] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [donorawardmodification] ADD FOREIGN KEY ([modificationtypeid]) REFERENCES [modificationtype] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [donorawardmodification] ADD FOREIGN KEY ([modifiedby]) REFERENCES [userinfo] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO

-- ----------------------------
-- Foreign Key structure for table [donorawardstatus]
-- ----------------------------
ALTER TABLE [donorawardstatus] ADD FOREIGN KEY ([createdby]) REFERENCES [userinfo] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [donorawardstatus] ADD FOREIGN KEY ([lastupdatedby]) REFERENCES [userinfo] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO

-- ----------------------------
-- Foreign Key structure for table [donorawardstatustransitionsallowed]
-- ----------------------------
ALTER TABLE [donorawardstatustransitionsallowed] ADD FOREIGN KEY ([createdby]) REFERENCES [userinfo] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [donorawardstatustransitionsallowed] ADD FOREIGN KEY ([goingtostatus]) REFERENCES [donorawardstatus] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [donorawardstatustransitionsallowed] ADD FOREIGN KEY ([lastupdatedby]) REFERENCES [userinfo] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [donorawardstatustransitionsallowed] ADD FOREIGN KEY ([startingfromstatus]) REFERENCES [donorawardstatus] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO

-- ----------------------------
-- Foreign Key structure for table [donorawardtype]
-- ----------------------------
ALTER TABLE [donorawardtype] ADD FOREIGN KEY ([createdby]) REFERENCES [userinfo] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [donorawardtype] ADD FOREIGN KEY ([lastupdatedby]) REFERENCES [userinfo] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO

-- ----------------------------
-- Foreign Key structure for table [fieldoffice]
-- ----------------------------
ALTER TABLE [fieldoffice] ADD FOREIGN KEY ([countryid]) REFERENCES [country] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [fieldoffice] ADD FOREIGN KEY ([countryid]) REFERENCES [country] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [fieldoffice] ADD FOREIGN KEY ([createdby]) REFERENCES [userinfo] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [fieldoffice] ADD FOREIGN KEY ([lastupdatedby]) REFERENCES [userinfo] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [fieldoffice] ADD FOREIGN KEY ([regionid]) REFERENCES [region] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [fieldoffice] ADD FOREIGN KEY ([regionid]) REFERENCES [region] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [fieldoffice] ADD FOREIGN KEY ([stateprovince]) REFERENCES [stateprovince] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO

-- ----------------------------
-- Foreign Key structure for table [imp]
-- ----------------------------
ALTER TABLE [imp] ADD FOREIGN KEY ([auditsid]) REFERENCES [audits] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [imp] ADD FOREIGN KEY ([createdby]) REFERENCES [userinfo] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [imp] ADD FOREIGN KEY ([lastupdatedby]) REFERENCES [userinfo] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [imp] ADD FOREIGN KEY ([projectid]) REFERENCES [project] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [imp] ADD FOREIGN KEY ([projectreportsid]) REFERENCES [projectreports] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO

-- ----------------------------
-- Foreign Key structure for table [keyword]
-- ----------------------------
ALTER TABLE [keyword] ADD FOREIGN KEY ([createdby]) REFERENCES [userinfo] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [keyword] ADD FOREIGN KEY ([lastupdatedby]) REFERENCES [userinfo] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO

-- ----------------------------
-- Foreign Key structure for table [modificationtype]
-- ----------------------------
ALTER TABLE [modificationtype] ADD FOREIGN KEY ([createdby]) REFERENCES [userinfo] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [modificationtype] ADD FOREIGN KEY ([lastupdatedby]) REFERENCES [userinfo] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO

-- ----------------------------
-- Foreign Key structure for table [organizationtype]
-- ----------------------------
ALTER TABLE [organizationtype] ADD FOREIGN KEY ([createdby]) REFERENCES [userinfo] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [organizationtype] ADD FOREIGN KEY ([lastupdatedby]) REFERENCES [userinfo] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO

-- ----------------------------
-- Foreign Key structure for table [partner]
-- ----------------------------
ALTER TABLE [partner] ADD FOREIGN KEY ([countryid]) REFERENCES [country] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [partner] ADD FOREIGN KEY ([countryid]) REFERENCES [country] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [partner] ADD FOREIGN KEY ([createdby]) REFERENCES [userinfo] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [partner] ADD FOREIGN KEY ([lastupdatedby]) REFERENCES [userinfo] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [partner] ADD FOREIGN KEY ([stateid]) REFERENCES [stateprovince] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO

-- ----------------------------
-- Foreign Key structure for table [payee]
-- ----------------------------
ALTER TABLE [payee] ADD FOREIGN KEY ([countryid]) REFERENCES [country] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [payee] ADD FOREIGN KEY ([countryid]) REFERENCES [country] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [payee] ADD FOREIGN KEY ([createdby]) REFERENCES [userinfo] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [payee] ADD FOREIGN KEY ([lastupdatedby]) REFERENCES [userinfo] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [payee] ADD FOREIGN KEY ([payeestateid]) REFERENCES [stateprovince] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO

-- ----------------------------
-- Foreign Key structure for table [paymentrequest]
-- ----------------------------
ALTER TABLE [paymentrequest] ADD FOREIGN KEY ([countryid]) REFERENCES [country] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [paymentrequest] ADD FOREIGN KEY ([countryid]) REFERENCES [country] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [paymentrequest] ADD FOREIGN KEY ([createdby]) REFERENCES [userinfo] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [paymentrequest] ADD FOREIGN KEY ([lastupdatedby]) REFERENCES [userinfo] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [paymentrequest] ADD FOREIGN KEY ([payeeid]) REFERENCES [payee] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [paymentrequest] ADD FOREIGN KEY ([payeeid]) REFERENCES [payee] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [paymentrequest] ADD FOREIGN KEY ([paymenttypeid]) REFERENCES [paymenttype] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [paymentrequest] ADD FOREIGN KEY ([paymenttypeid]) REFERENCES [paymenttype] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO

-- ----------------------------
-- Foreign Key structure for table [paymentrequestlineitem]
-- ----------------------------
ALTER TABLE [paymentrequestlineitem] ADD FOREIGN KEY ([accountcodeid]) REFERENCES [accountcode] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [paymentrequestlineitem] ADD FOREIGN KEY ([createdby]) REFERENCES [userinfo] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [paymentrequestlineitem] ADD FOREIGN KEY ([lastupdatedby]) REFERENCES [userinfo] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [paymentrequestlineitem] ADD FOREIGN KEY ([paymentrequestid]) REFERENCES [paymentrequest] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [paymentrequestlineitem] ADD FOREIGN KEY ([projectid]) REFERENCES [project] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO

-- ----------------------------
-- Foreign Key structure for table [paymentrequestmodification]
-- ----------------------------
ALTER TABLE [paymentrequestmodification] ADD FOREIGN KEY ([modificationtypeid]) REFERENCES [modificationtype] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [paymentrequestmodification] ADD FOREIGN KEY ([paymentrequestid]) REFERENCES [paymentrequest] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO

-- ----------------------------
-- Foreign Key structure for table [paymentrequeststatus]
-- ----------------------------
ALTER TABLE [paymentrequeststatus] ADD FOREIGN KEY ([createdby]) REFERENCES [userinfo] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [paymentrequeststatus] ADD FOREIGN KEY ([lastupdatedby]) REFERENCES [userinfo] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [paymentrequeststatus] ADD FOREIGN KEY ([paymentrequestid]) REFERENCES [paymentrequest] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [paymentrequeststatus] ADD FOREIGN KEY ([paymentstatustypeid]) REFERENCES [paymentstatustype] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO

-- ----------------------------
-- Foreign Key structure for table [paymentrequeststatustransitions]
-- ----------------------------
ALTER TABLE [paymentrequeststatustransitions] ADD FOREIGN KEY ([createdby]) REFERENCES [userinfo] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [paymentrequeststatustransitions] ADD FOREIGN KEY ([goingtostatus]) REFERENCES [paymentstatustype] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [paymentrequeststatustransitions] ADD FOREIGN KEY ([lastupdatedby]) REFERENCES [userinfo] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [paymentrequeststatustransitions] ADD FOREIGN KEY ([startingfromstatus]) REFERENCES [paymentstatustype] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO

-- ----------------------------
-- Foreign Key structure for table [paymentstatustype]
-- ----------------------------
ALTER TABLE [paymentstatustype] ADD FOREIGN KEY ([createdby]) REFERENCES [userinfo] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [paymentstatustype] ADD FOREIGN KEY ([lastupdatedby]) REFERENCES [userinfo] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO

-- ----------------------------
-- Foreign Key structure for table [paymenttype]
-- ----------------------------
ALTER TABLE [paymenttype] ADD FOREIGN KEY ([createdby]) REFERENCES [userinfo] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [paymenttype] ADD FOREIGN KEY ([lastupdatedby]) REFERENCES [userinfo] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO

-- ----------------------------
-- Foreign Key structure for table [project]
-- ----------------------------
ALTER TABLE [project] ADD FOREIGN KEY ([auditsid]) REFERENCES [audits] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [project] ADD FOREIGN KEY ([countryid]) REFERENCES [country] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [project] ADD FOREIGN KEY ([countryid]) REFERENCES [country] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [project] ADD FOREIGN KEY ([createdby]) REFERENCES [userinfo] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [project] ADD FOREIGN KEY ([donorawardid]) REFERENCES [donoraward] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [project] ADD FOREIGN KEY ([donorawardid]) REFERENCES [donoraward] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [project] ADD FOREIGN KEY ([lastupdatedby]) REFERENCES [userinfo] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [project] ADD FOREIGN KEY ([masterprojectid]) REFERENCES [project] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [project] ADD FOREIGN KEY ([organizationtypeid]) REFERENCES [organizationtype] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [project] ADD FOREIGN KEY ([organizationtypeid]) REFERENCES [organizationtype] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [project] ADD FOREIGN KEY ([parentprojectid]) REFERENCES [project] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [project] ADD FOREIGN KEY ([partnerid]) REFERENCES [partner] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [project] ADD FOREIGN KEY ([partnerid]) REFERENCES [partner] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [project] ADD FOREIGN KEY ([programofficer]) REFERENCES [userinfo] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [project] ADD FOREIGN KEY ([programassistant]) REFERENCES [userinfo] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [project] ADD FOREIGN KEY ([programofficer]) REFERENCES [userinfo] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [project] ADD FOREIGN KEY ([programassistant]) REFERENCES [userinfo] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [project] ADD FOREIGN KEY ([projectstatusid]) REFERENCES [projectstatus] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [project] ADD FOREIGN KEY ([projecttypeid]) REFERENCES [projecttype] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [project] ADD FOREIGN KEY ([projectstatusid]) REFERENCES [projectstatus] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [project] ADD FOREIGN KEY ([projecttypeid]) REFERENCES [projecttype] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [project] ADD FOREIGN KEY ([regionaldirector]) REFERENCES [userinfo] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [project] ADD FOREIGN KEY ([regionaldirector]) REFERENCES [userinfo] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [project] ADD FOREIGN KEY ([regionid]) REFERENCES [region] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [project] ADD FOREIGN KEY ([regionid]) REFERENCES [region] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO

-- ----------------------------
-- Foreign Key structure for table [projectkeyword]
-- ----------------------------
ALTER TABLE [projectkeyword] ADD FOREIGN KEY ([createdby]) REFERENCES [userinfo] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [projectkeyword] ADD FOREIGN KEY ([keywordid]) REFERENCES [keyword] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [projectkeyword] ADD FOREIGN KEY ([lastupdatedby]) REFERENCES [userinfo] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [projectkeyword] ADD FOREIGN KEY ([projectid]) REFERENCES [project] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO

-- ----------------------------
-- Foreign Key structure for table [projectmodification]
-- ----------------------------
ALTER TABLE [projectmodification] ADD FOREIGN KEY ([createdby]) REFERENCES [userinfo] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [projectmodification] ADD FOREIGN KEY ([lastupdatedby]) REFERENCES [userinfo] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [projectmodification] ADD FOREIGN KEY ([modifiedby]) REFERENCES [userinfo] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [projectmodification] ADD FOREIGN KEY ([modificationtypeid]) REFERENCES [modificationtype] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [projectmodification] ADD FOREIGN KEY ([projectid]) REFERENCES [project] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO

-- ----------------------------
-- Foreign Key structure for table [projectreports]
-- ----------------------------
ALTER TABLE [projectreports] ADD FOREIGN KEY ([createdby]) REFERENCES [userinfo] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [projectreports] ADD FOREIGN KEY ([lastupdatedby]) REFERENCES [userinfo] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [projectreports] ADD FOREIGN KEY ([projectid]) REFERENCES [project] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO

-- ----------------------------
-- Foreign Key structure for table [projectstatus]
-- ----------------------------
ALTER TABLE [projectstatus] ADD FOREIGN KEY ([createdby]) REFERENCES [userinfo] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [projectstatus] ADD FOREIGN KEY ([lastupdatedby]) REFERENCES [userinfo] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO

-- ----------------------------
-- Foreign Key structure for table [projectstatuschangelog]
-- ----------------------------
ALTER TABLE [projectstatuschangelog] ADD FOREIGN KEY ([createdby]) REFERENCES [userinfo] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [projectstatuschangelog] ADD FOREIGN KEY ([lastupdatedby]) REFERENCES [userinfo] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [projectstatuschangelog] ADD FOREIGN KEY ([newprojectstatusid]) REFERENCES [projectstatus] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [projectstatuschangelog] ADD FOREIGN KEY ([oldprojectstatusid]) REFERENCES [projectstatus] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [projectstatuschangelog] ADD FOREIGN KEY ([projectid]) REFERENCES [project] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO

-- ----------------------------
-- Foreign Key structure for table [projectstatustransitionsallowed]
-- ----------------------------
ALTER TABLE [projectstatustransitionsallowed] ADD FOREIGN KEY ([createdby]) REFERENCES [userinfo] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [projectstatustransitionsallowed] ADD FOREIGN KEY ([goingtostatus]) REFERENCES [projectstatus] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [projectstatustransitionsallowed] ADD FOREIGN KEY ([lastupdatedby]) REFERENCES [userinfo] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [projectstatustransitionsallowed] ADD FOREIGN KEY ([startingfromstatus]) REFERENCES [projectstatus] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO

-- ----------------------------
-- Foreign Key structure for table [projecttype]
-- ----------------------------
ALTER TABLE [projecttype] ADD FOREIGN KEY ([createdby]) REFERENCES [userinfo] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [projecttype] ADD FOREIGN KEY ([lastupdatedby]) REFERENCES [userinfo] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO

-- ----------------------------
-- Foreign Key structure for table [region]
-- ----------------------------
ALTER TABLE [region] ADD FOREIGN KEY ([createdby]) REFERENCES [userinfo] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [region] ADD FOREIGN KEY ([lastupdatedby]) REFERENCES [userinfo] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [region] ADD FOREIGN KEY ([regionaldirector]) REFERENCES [userinfo] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [region] ADD FOREIGN KEY ([regionaldirector]) REFERENCES [userinfo] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO

-- ----------------------------
-- Foreign Key structure for table [role]
-- ----------------------------
ALTER TABLE [role] ADD FOREIGN KEY ([createdby]) REFERENCES [userinfo] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [role] ADD FOREIGN KEY ([lastupdatedby]) REFERENCES [userinfo] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO

-- ----------------------------
-- Foreign Key structure for table [roleprivileges]
-- ----------------------------
ALTER TABLE [roleprivileges] ADD FOREIGN KEY ([createdby]) REFERENCES [userinfo] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [roleprivileges] ADD FOREIGN KEY ([lastupdatedby]) REFERENCES [userinfo] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [roleprivileges] ADD FOREIGN KEY ([roleid]) REFERENCES [role] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [roleprivileges] ADD FOREIGN KEY ([link]) REFERENCES [link] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO

-- ----------------------------
-- Foreign Key structure for table [stateprovince]
-- ----------------------------
ALTER TABLE [stateprovince] ADD FOREIGN KEY ([countryid]) REFERENCES [country] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [stateprovince] ADD FOREIGN KEY ([createdby]) REFERENCES [userinfo] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [stateprovince] ADD FOREIGN KEY ([lastupdatedby]) REFERENCES [userinfo] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO

-- ----------------------------
-- Foreign Key structure for table [useractionalertschanges]
-- ----------------------------
ALTER TABLE [useractionalertschanges] ADD FOREIGN KEY ([readby]) REFERENCES [userinfo] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO

-- ----------------------------
-- Foreign Key structure for table [userinfo]
-- ----------------------------
ALTER TABLE [userinfo] ADD FOREIGN KEY ([createdby]) REFERENCES [userinfo] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [userinfo] ADD FOREIGN KEY ([fieldofficeid]) REFERENCES [fieldoffice] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [userinfo] ADD FOREIGN KEY ([fieldofficeid]) REFERENCES [fieldoffice] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [userinfo] ADD FOREIGN KEY ([lastupdatedby]) REFERENCES [userinfo] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [userinfo] ADD FOREIGN KEY ([preferenceid]) REFERENCES [userpreference] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [userinfo] ADD FOREIGN KEY ([regionid]) REFERENCES [region] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [userinfo] ADD FOREIGN KEY ([regionid]) REFERENCES [region] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [userinfo] ADD FOREIGN KEY ([usertypeid]) REFERENCES [usertype] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [userinfo] ADD FOREIGN KEY ([usertypeid]) REFERENCES [usertype] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO

-- ----------------------------
-- Foreign Key structure for table [userrole]
-- ----------------------------
ALTER TABLE [userrole] ADD FOREIGN KEY ([createdby]) REFERENCES [userinfo] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [userrole] ADD FOREIGN KEY ([lastupdatedby]) REFERENCES [userinfo] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [userrole] ADD FOREIGN KEY ([roleid]) REFERENCES [role] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [userrole] ADD FOREIGN KEY ([userinfoid]) REFERENCES [userinfo] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO

-- ----------------------------
-- Foreign Key structure for table [usertype]
-- ----------------------------
ALTER TABLE [usertype] ADD FOREIGN KEY ([createdby]) REFERENCES [userinfo] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
ALTER TABLE [usertype] ADD FOREIGN KEY ([lastupdatedby]) REFERENCES [userinfo] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
